VBA If statements Variable help!

[KIDRoach]

I'm kinda new to VBA and I've searched through google plenty of times with no results so here I go....


How do I use an if statements in vba for excel's macro?
So let's say I have this number "105200".

I want an if statement that checks if the number in cell "H1" is the same as "105200". The thing is, this "105200" number changes for every file, to maybe "105600" or "109300", so it's easier if I ask the user for the "105200" number, and then set it as let's say "x".

When I do this:


If Cells(1,1)= "105200" Then
Sheets(result).Select
Cells(newcount, 2).Select
ActiveSheet.Paste
ElseIf Cells(1,1)= "109200" Then
Sheets(result).Select
Cells(newcount, 3).Select
ActiveSheet.Paste
End If

The macro worked...

Then, I changed the "105200" to x and defined x = "105200" as such:

x1 = "105200"
x2 = "109200"

If Cells(1,1)= x1 Then
Sheets(result).Select
Cells(newcount, 2).Select
ActiveSheet.Paste
ElseIf Cells(1,1)= x2 Then
Sheets(result).Select
Cells(newcount, 3).Select
ActiveSheet.Paste
End If


and it doesn't work... I've been looking at this thing for 6 hours and can't figure out why...

What should I put as x ?

[mdmackillop]

There is obviously a distinction between String and Numerical values. I can't explain it here, but the simplest way to avoid such issues is to use Option Explicit and declare all your variables with types as appropriate.

BTW, it's best to post the whole of the code.


Dim x1 As Long, x2 As Long
Dim NewCount As Long
Dim Result As String

x1 = 105200
x2 = 109200
NewCount = 3
Result = "Sheet3"
If Cells(1, 1) = x1 Then
Sheets(Result).Select
Cells(NewCount, 2).Select
ActiveSheet.Paste
ElseIf Cells(1, 1) = x2 Then
Sheets(Result).Select
Cells(NewCount, 3).Select
ActiveSheet.Paste
End If

[KIDRoach]

^Thanks for replying!

I didn't really understand the use of "dimensioning" the variables, so I didn't do it, but I guess I'll start now... :D

I didn't think of attaching the full code since the code is pretty long and it can get confusing, but this is the full code:
Sub Conversion()
'
' Conversion Macro
'


''''''''''''Input Data'''''''''

Dim datasheet As String
datasheet = Application.InputBox("Enter data - recommend " & _
Sheets("Dest").Range("C10").Text, "Enter data", Sheets("Dest").Range("C10").Text)
If strMsg <> "" Then Sheets("Dest").Range("C11").Value = strMsg




'datasheet = "Name of Data Sheet"
datasheet = "Sheet1"
'result = "Name of Result Sheet"
result = "conv_data"





'''''''''''Input Done'''''''''''''




'''''' Sorting data based on City/Town/County Name

ActiveWorkbook.Worksheets(datasheet).sort.SortFields.Clear
ActiveWorkbook.Worksheets(datasheet).sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(datasheet).sort
.SetRange Range("A1:AZ100000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'''''''''''Sort Done'''''''




''''Delete Useless Rows Part 1 '''''''''''''
x = 1
Sheets(datasheet).Select
Cells(1, 1).Select
Do While IsEmpty(Cells(x, 1)) = False
If Cells(x, 4) <> "FUND CENTER" Then
Rows(x).Select
Selection.Delete
x = x - 1
End If
x = x + 1
Loop

Dim usedrows As Long
usedrows = ActiveSheet.UsedRange.Rows.Count

Do While x <= usedrows
Rows(x).Select
Selection.Delete
usedrows = ActiveSheet.UsedRange.Rows.Count
Loop
'''''''''Delete Done''''''''''''




'''''' Re-Sorting data based on Number

ActiveWorkbook.Worksheets(datasheet).sort.SortFields.Clear
ActiveWorkbook.Worksheets(datasheet).sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(datasheet).sort
.SetRange Range("A1:AZ100000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'''''''''''Re-Sort Done'''''''


'''''''''Transposing'''''''''''''''

Sheets(datasheet).Select
x = 1
newcount = 0
county = "blank"
Do While IsEmpty(Cells(x, 4)) = False
Sheets(datasheet).Select
If Cells(x, 4) = "FUND CENTER" Then
Sheets(datasheet).Select
If county <> Cells(x, 2) Then
newcount = newcount + 1
county = Cells(x, 2)
Cells(x, 2).Select
Selection.Copy
Sheets(result).Select
Cells(newcount, 1).Select
ActiveSheet.Paste

End If
Sheets(datasheet).Select
Cells(x, 9).Select
Selection.Copy

If Cells(x, 8) = "105200" Then
Sheets(result).Select
Cells(newcount, 2).Select
ActiveSheet.Paste
ElseIf Cells(x, 8) = "192500" Then
Sheets(result).Select
Cells(newcount, 3).Select
ActiveSheet.Paste
ElseIf Cells(x, 8) = "192610" Then
Sheets(result).Select
Cells(newcount, 4).Select
ActiveSheet.Paste
ElseIf Cells(x, 8) = "130000" Then
Sheets(result).Select
Cells(newcount, 5).Select
ActiveSheet.Paste
ElseIf Cells(x, 8) = "" Then
Sheets(result).Select
Cells(newcount, 6).Select
ActiveSheet.Paste
ElseIf Cells(x, 8) = "" Then
Sheets(result).Select
Cells(newcount, 7).Select
ActiveSheet.Paste
ElseIf Cells(x, 8) = "" Then
Sheets(result).Select
Cells(newcount, 8).Select
ActiveSheet.Paste
ElseIf Cells(x, 8) = "" Then
Sheets(result).Select
Cells(newcount, 9).Select
ActiveSheet.Paste

End If

x = x + 1
Sheets(datasheet).Select
End If
Loop


''''''''''''''''Transposing Done''''''''''''''



''''''''''''Input Sum''''
Sheets(result).Select
usedrows = ActiveSheet.UsedRange.Rows.Count
Cells(1, 10).Select
ActiveCell.FormulaR1C1 = "=SUM(RC2:RC9)"
Selection.AutoFill Destination:=Range("J1:J" & Range("A" & Rows.Count).End(xlUp).Row)
'''''''''Sum Done'''''''''''


'''''''''''''Select Counties''''''''''
Cells(1, 1).Select
x = 1
Do While IsEmpty(Cells(x, 1)) = False
Cells(x, 1).Select
If InStr(Cells(x, 1), "CTY T") > 0 Then
Cells(x, 11).Select
ActiveCell.FormulaR1C1 = "County"
ElseIf InStr(Cells(x, 1), "COUNTY") > 0 Then
Cells(x, 11).Select
ActiveCell.FormulaR1C1 = "County"
End If
x = x + 1
Loop
'''''''''''''Done Selection''''''''''



'''''''''Reset Row Numbers''''''''''''
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
''''''''''Reset Done''''''''''''


''''''''Insert Blank Cell'''''''''
x = 1
usedrows = ActiveSheet.UsedRange.Rows.Count

Do While x <= usedrows
If InStr(Cells(x, 11), "County") > 0 Then
Rows(x).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
x = x + 2
Rows(x).Select
Selection.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove

End If
usedrows = ActiveSheet.UsedRange.Rows.Count
x = x + 1
Loop
''''''''Insert Blank Cell Done'''''''''


''''''''''Highlighting'''''''''
x = 1
Do While x <= usedrows
Cells(x, 1).Select
If InStr(Cells(x, 11), "County") > 0 Then
Rows(x).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End If
usedrows = ActiveSheet.UsedRange.Rows.Count
x = x + 1
Loop
''''''''''''Highlight Done'''''''''''



End Sub

As you can see, I was trying to get input data from the user, in the beginning of the vba, but can't figure out why using the variable instead of the number itself won't work. I'll try the fix tomorrow and see if it works.

Thanks again!

[geekgirlau]

As MD pointed out, Excel sees numbers and text as completely different: "1" does NOT equal 1. If you tried to compare the 2 values, Excel will tell you that they don't match.

When you use a variable, you should always dimension it - this is the "Dim x1 as Long" at the start of the procedure. Dimensioning your variables forces you to declare the type of data that will go into the variable (string, long integer etc.) and sets aside the required amount of space for that data type.

If you add Option Explicit at the top of the module, it prevents you from making silly typing errors that take forever to track down. For example, if you accidently type "x11" as the name of your variable instead of "x1", Option Explicit will pick this up immediately, rather than you trawling through your code trying to work out where the problem is.

Another hint that you'll see frequently on the board is that you don't have to actually select cells to do something with them. For example,

Range("A1").Select
Activecell.Formula = "100"

can also be written as

Range("A1").Formula = "100"

However I would suggest that you get your logic right first, then think about streamlining the code somewhat. It does take a little while to wrap your head around this stuff!