CODEWITHSUNDEEP
excelvba
Kalma Kun
Kalma Kun

Reputation: 19

Trying to add elseif statement in excel-vba will not paste or copy

Trying to add elseif statement but in excel it does not copy and paste from sheet 3 or sheet4 what did i do wrong about this code?

Dim i, LastRow

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row
Sheets("Sheet2").Range("A2:I500").ClearContents
For i = 2 To LastRow

If UCase(Sheets("Sheet1").Cells(i, "B").Value) = "Hong Kong" Then
Sheets("Sheet1").Cells(i, "D").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

ElseIf UCase(Sheets("Sheet1").Cells(i, "B").Value) = "United Kingdom" Then
Sheets("Sheet1").Cells(i, "D").EntireRow.Copy Destination:=Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)

ElseIf UCase(Sheets("Sheet1").Cells(i, "B").Value) = "New zealand" Then
Sheets("Sheet1").Cells(i, "D").EntireRow.Copy Destination:=Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)

ElseIf UCase(Sheets("Sheet1").Cells(i, "B").Value) = "Australia" Then
Sheets("Sheet1").Cells(i, "D").EntireRow.Copy Destination:=Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)

End If

Next i


End Sub

Upvotes: 0

Views: 42

Answers (2)

Xabier
Xabier

Reputation: 7735

Further to the answer provided by YowE3K, you didn't declare your variables, please see below:

Dim i As Long
Dim LastRow As Long
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'above declare variables

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
'get the last row with data on Sheet1 Column A

Sheets("Sheet2").Range("A2:I500").ClearContents
'clear Sheet2's given range

For i = 2 To LastRow 'loop from row 2 to last
    If UCase(Sheets("Sheet1").Cells(i, "B").Value) = "HONG KONG" Then 
   'you can either compare against HONG KONG or like below UCase("Hong Kong")
        ws.Cells(i, "D").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    ElseIf UCase(Sheets("Sheet1").Cells(i, "B").Value) = UCase("United Kingdom") Then
        ws.Cells(i, "D").EntireRow.Copy Destination:=Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
    ElseIf UCase(Sheets("Sheet1").Cells(i, "B").Value) = UCase("New zealand") Then
        ws.Cells(i, "D").EntireRow.Copy Destination:=Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)
    ElseIf UCase(Sheets("Sheet1").Cells(i, "B").Value) = UCase("Australia") Then
        ws.Cells(i, "D").EntireRow.Copy Destination:=Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i
End Sub

Upvotes: 0

YowE3K
YowE3K

Reputation: 23994

Your If statements, such as If UCase(Sheets("Sheet1").Cells(i, "B").Value) = "Hong Kong" Then are comparing something that has been converted to upper case with something that is definitely NOT upper case, so will never be True.

For instance, if cell B5 contained "hong kong", when you do UCase of that you get "HONG KONG". A comparison of "HONG KONG" = "Hong Kong" will equate to False.

You should use statements such as

If UCase(Sheets("Sheet1").Cells(i, "B").Value) = "HONG KONG" Then

or, if you are lazy and don't want to press the Caps Lock key while typing, you could use

If UCase(Sheets("Sheet1").Cells(i, "B").Value) = UCase("Hong Kong") Then

Either of those two will ensure that both sides of the comparison are in upper case.

Upvotes: 1

Related Questions