Answer all if statements before looping

[wafs]

I need this to go through all if statements before it loops. The macro look at the one if statement and goes to the end and loops. I need the macro to do mutliple if statements first then increment and loop. Any suggestions on how to do this.


'Output into destination sheet

Sheets("data").Select
Range("A1:A400").AdvancedFilter xlFilterInPlace, CriteriaRange:=Range("A1:A400"), Unique:=True
Columns("A:A").Select
Selection.Copy
Sheets("Main Page").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("data").ShowAllData
Do
Set myrange = Sheets(destsh).Range(output(13))
p = p + 1
m = p + 1
vRet = Application.WorksheetFunction.VLookup(myrange, Worksheets("data").Range("A2:G300"), 2, False)
reportlocation = "A" + m 'partnumber
Sheets(destsh).Range(reportlocation) = vRet
If UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(9)))) Then
reportlocation = "D" + m 'TP
Sheets(destsh).Range(reportlocation) = strvar1
ElseIf UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(5)))) Then
reportlocation = "E" + m 'ROLL
Sheets(destsh).Range(reportlocation) = strvar1
End If
n = n + 1
TestRange3 = "A" + n
TestRange = "E" + n
output(1) = "A" + n
output(13) = "B" + n
Loop Until Sheets(destsh).Range(output(13)) = ""

 

[Bong]

I think you have to use separate If..End If blocks. What you have will allow the If block to terminate if the first condition is true (no need for to evaluate the Else part).

So, something like

                    If UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(9)))) Then
                        reportlocation = "D" + m 'TP
                        Sheets(destsh).Range(reportlocation) = strvar1
                    End If
                    If UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(5)))) Then
                        reportlocation = "E" + m 'ROLL
                        Sheets(destsh).Range(reportlocation) = strvar1
                    End If

_________________
Bob Rashkin

 

[wafs]

But I need to evaluate all if statements even if some are comditions are false. Below is the spreadsheet I'm trying to automate.

Part Number LotNumber WSH TP ROLL HT Ship
11588715 1428 X X
11500718 1392

 

[fumei]

Maybe use Case statements instead?

Gerry

 

[wafs]

I have it figured out and working. Below is the final code if anyone is interested.

Sub copyinfo_to_main_page()
'
' copyinfo Macro
' Moves ID number over and places an X where the part has been scanned
' Macro recorded 4/7/2006 by Wendy Smith
'
Dim Cellstart As Integer
Dim p As Integer
Dim x As Integer
Dim n As String 'counting variable
Dim m As String
Dim TestRange As String 'to process
Dim TestRange2 As String 'part number
Dim TestRange3 As String 'id number
Dim sh As String 'source sheet
Dim destsh As String 'destination sheet
Dim reportlocation As String
Dim output(1 To 14) As String
Dim strvar1 As String
Dim vRet As Variant
Dim myrange As Range
Dim rsearch As Range

Cellstart = 2 'starting point of source sheet
p = 0
strvar1 = "X"

sh = "data" 'work sheet where the part info is kept
destsh = "Main Page" 'work sheet where part info is broken out

'Clear out old Data and unhide rows
Sheets("Main Page").Select
Sheets(destsh).Range("A2:h400").Select
Selection.ClearContents

'Retreive data from source sheet
n = Cellstart

TestRange = "E" + n 'to process source sheet
TestRange2 = "B" + n 'part # source sheet
TestRange3 = "A" + n 'ID number source sheet
output(1) = "A" + n 'part number
output(2) = Sheets(destsh).Range(output(1)).Value
output(3) = "C1" 'WASH
output(4) = Sheets(destsh).Range(output(3)).Value
output(9) = "D1" 't/p
output(10) = Sheets(destsh).Range(output(9)).Value
output(5) = "E1" 'Roll
output(6) = Sheets(destsh).Range(output(5)).Value
output(7) = "F1" 'HT
output(8) = Sheets(destsh).Range(output(7)).Value
output(11) = "G1" 'ship
output(12) = Sheets(destsh).Range(output(11)).Value
output(13) = "B" + n 'ID Number
output(14) = Sheets(destsh).Range(output(13)).Value

'Output into destination sheet

Sheets("data").Select
Range("A1:A400").AdvancedFilter xlFilterInPlace, CriteriaRange:=Range("A1:A400"), Unique:=True
Columns("A:A").Select
Selection.Copy
Sheets("Main Page").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("data").ShowAllData
p = p + 1
m = p + 1
Do
Set myrange = Sheets(destsh).Range(output(13))

vRet = Application.WorksheetFunction.VLookup(myrange, Worksheets("data").Range("A2:G300"), 2, False)
reportlocation = "A" + m 'partnumber
Sheets(destsh).Range(reportlocation) = vRet
If UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(3)))) Then
reportlocation = "C" + m 'WSH
Sheets(destsh).Range(reportlocation) = strvar1
ElseIf UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(9)))) Then
reportlocation = "D" + m 'TP
Sheets(destsh).Range(reportlocation) = strvar1
ElseIf UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(5)))) Then
reportlocation = "E" + m 'ROLL
Sheets(destsh).Range(reportlocation) = strvar1
ElseIf UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(7)))) Then
reportlocation = "F" + m 'HT
Sheets(destsh).Range(reportlocation) = strvar1
ElseIf UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) And UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(11)))) Then
reportlocation = "G" + m 'Ship
Sheets(destsh).Range(reportlocation) = strvar1
ElseIf UCase(Trim(Sheets(sh).Range(TestRange3))) <> UCase(Trim(Sheets(destsh).Range(output(13)))) Then
p = p + 1
m = p + 1
output(13) = "B" + m 'Destination sheet ID
n = n - 1
TestRange3 = "A" + n 'Source Sheet ID
End If
n = n + 1
TestRange = "E" + n 'Source sheet process
TestRange3 = "A" + n 'Source sheet ID
Loop Until Sheets(destsh).Range(output(13)) = ""
End Sub