CODEWITHSUNDEEP
vbaexcel
Jon Rose
Jon Rose

Reputation: 139

Substitute Excel Formula Argument (provided by the user) with a Variable

Concept: Entire Rows are deleted through a macro based off parameters which are represented as an excel formula by the user. The idea here is that a user can use Boolean formulas that they're already familiar with to evaluate values in a range (read the "Process" below for further clarification).

Process: A user clicks on a button which shows a form. This form contains two input fields (or parameters); "Column" and "Formula". The "Column" is the range for which the macro will be cycling through (let's say $A:$A). The "Formula" is an Excel based formula represented as such, in the user parameter field ie =OR(A1="X",A1="Y"). However, I've instructed the user to replace any instance of A1 with rng. I've requested the user to do this because the idea here is that I would replace rng with a changing variable in VBA that cycles through all the cells specified in the "Column" parameter.

Problem: I'm not aware of any way to replace the rng representation within the Excel formula with a range variable in VBA.

Update 4-7-17 Thank you all for your responses but I'm pretty certain my problem is getting lost in translation. I'm aware this is my fault, since I didn't provide any code for analysis. Unfortunately, therein lies the issue. I don't know what to write. I'm going to do my best to write some code (that I know is wrong) which will hopefully convey what I'm trying to accomplish.

Sample Code 4-7-17

Sub SampleCode()
Dim wRng As Range
    Set wRng = Range("A1:A26") 'Let's assume that the values in this range are the
                                ' letters of the alphabet
Dim Counter As Integer


'Cell "B2" will contain a formula that the user has entered
'which is:  =OR(rng="X",rng="M")
'Obviously the formula returns an error in excel (#NAME? to be
'exact) but that's understood.
Dim wFormula As String
    wFormula = Range("B2").Formula

Dim rng As Range 'This variable "rng" is what is represented in the
'formula that was written in Range("B2")

'*** This is where I get stuck.  I know I'm missing code here to
'be able to proceed with my routine below.
'code
'code
'code

Counter = wRng.Rows.Count
For i = 1 To wRng.Rows.Count
    Set rng = Cells(Counter, 1)
    If wFormula = True Then
        rng.EntireRow.Delete
    End If
    Counter = Counter - 1
Next i

'The ending result should be that row 24 was deleted because it contained
'the letter "X" and row 13 was deleted because it contained the letter "M"
'
'The objective of this code is to use any Excel formula which evaluates out
'to a True or False value.

End Sub

Upvotes: 0

Views: 89

Answers (2)

Rajesh Sinha
Rajesh Sinha

Reputation: 197

Hey John this code will help you to get the solution,

Public Sub ProcessData() Const TEST_COLUMN As String = "A"
Dim Lastrow As Long Dim i As Long Application.ScreenUpdating = False

With ActiveSheet
Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For i = Lastrow To 1 Step -1

If Cells(i, TEST_COLUMN).Value2 Like "AU" Or _ Cells(i, TEST_COLUMN).Value2 Like "AZ" Then
.Rows(i).Delete
End If
Next i
End With
Application.ScreenUpdating = True End Sub

Upvotes: 0

Rajesh Sinha
Rajesh Sinha

Reputation: 197

Hey Jon first you need to declare a Variable of relevant data type, then pass value from Range & finally use where you wish to, like,

Dim Src As Variant Src= Sheets( "Sheet3" ).Range( "A2:A9" ).Value

Upvotes: 0

Related Questions