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