mercredi 20 mai 2015

Excel VBA 2010 - If-Then-ElseIf Statement not Recognizing String Variables

First question on Stackoverflow. I have an Excel VBA macro with an If-Then-ElseIf statement that is within a For-Next Loop. I'll show the code before describing the issue. I post the entire code just in case I'm wrong about thinking the problem lies within the If statement.

Code:

Option Explicit

Sub GetData()

Dim wsPasteTo As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long, i As Long

Set wsPasteTo = ThisWorkbook.Sheets("ACP")
NextRow = wsPasteTo.Range("A" & Rows.Count).End(xlUp).Row + 2

Set wbDATA = Workbooks.Open("\\cmicro.com\Shares\Amb\Amb-Probes\DataLogs\CQS-03-033-2012 Coax Shelf Cut Log R2.6.xlsm", ReadOnly:=True)
Application.ScreenUpdating = False

    With wbDATA.Sheets("ACP")

        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 2 To LastRow

            If Cells(i, "E") = "Angle" Then
                .Range("K2:L" & LastRow).Copy
                wsPasteTo.Range("I" & NextRow).PasteSpecial xlPasteValues

            ElseIf Cells(i, "E") = "Vertical" Then
                .Range("K2:L" & LastRow).Copy
                wsPasteTo.Range("D" & NextRow).PasteSpecial xlPasteValues

            Else
                .Range("K2:L" & LastRow).Copy
                wsPasteTo.Range("N" & NextRow).PasteSpecial xlPasteValues

            End If

        Next i

    End With

Application.ScreenUpdating = True
wbDATA.Close False

End Sub

This macro copies data from another excel workbook (let's call it the Copy Workbook) based on what's in column E of the Copy Workbook. Column E has 3 options for the user to choose: "Angle", "Vertical", and "n/a". I used data validation so the user must chose one of those 3 options from a drop down in all column E cells. Based on that, the macro pastes data into columns in a separate workbook, let's call it the Paste Workbook.

This code doesn't have errors, however all the data is being pasted into the wrong place in the Paste Workbook.

I think the problem is with the lines of code:

If Cells(i, "E") = "Angle" Then

and

ElseIf Cells(i, "E") = "Vertical" Then

because when I'm in debug mode, these lines are skipped as though the string variables "Vertical" and "Angle" aren't there in column "E". That's why all the data is going into one place.

I can't figure what is wrong with the code. I checked out column E in the Copy Workbook and there are no spelling/capitalization issues. Maybe a Case statement would be better for what I'm doing, but I'm not savvy with VBA and I don't know how to do that.

Phew, thanks for reading. I look forward to your responses.

-Nick

Aucun commentaire:

Enregistrer un commentaire