Consulting

Results 1 to 4 of 4

Thread: VBA If statements Variable help!

  1. #1
    VBAX Newbie
    Joined
    Jun 2010
    Posts
    2
    Location

    VBA If statements Variable help!

    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:

    [VBA]
    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[/VBA]

    The macro worked...

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

    x1 = "105200"
    x2 = "109200"
    [VBA]
    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[/VBA]


    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 ?

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.

    [vba]
    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

    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Newbie
    Joined
    Jun 2010
    Posts
    2
    Location
    ^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...

    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:
    [vba]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
    [/vba]
    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!

  4. #4
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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,

    [vba]Range("A1").Select
    Activecell.Formula = "100"[/vba]

    can also be written as

    [vba]Range("A1").Formula = "100"[/vba]

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •