How to mark x on same line

[wafs]

I created a program that goes through and reads where parts have been scanned at the plant. Now I need to change the code around to where it will put a part number on a line then put an X on the same line, but under different columns to show where the part has been scanned. Is this possible?

Part # Wash Trim Roll Heat Ship
ABC123 X X

 

[woja]

Is this in Excel?

[tt]_______________________________________
Roger
The Eileens are out there[/tt]

 

[xlbo]

certainly is (assuming excel) - what do you currently have and how does it need to change ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wafs]

Here is what the report looks like. I need to have the part number and id number show up once with the X under each column on the same line. The code is below and I need to either combine all my if statements or have my output changed.


Part Number ID Number WSH TP ROLL HT Ship
12562582 1434 X
12562582 1434 X


Sub copyinfo_to_main_page()
Dim Cellstart As Integer
Dim p As Integer
Dim r As Integer
Dim s As Integer
Dim t As Integer
Dim x As Integer
Dim n As String 'counting variable
Dim i As String 'counting variable
Dim y As String 'counting variable
Dim z As String 'coudnting 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

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

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


Sheets("Data").Select
Columns("B:B").Select
Range("A1:H300").sort key1:=Range("B1"), order1:=xlAscending, header:=xlGuess, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal

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

n = Cellstart

TestRange = "E" + n 'to process source sheet
TestRange2 = "B" + n 'part # source sheet
TestRange3 = "F" + 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

'Worksheets(sh).Range("B2:B1000").Copy Destination:=Worksheets(destsh).Range("A2") 'part number
'Worksheets(sh).Range("F2:F1000").Copy Destination:=Worksheets(destsh).Range("B2") 'id number

For x = 1 To 300 'increase this number only if data sheet has more than 300 parts
If UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(3)))) Then 'WSH
p = p + 1
m = p + 1
'If UCase(Trim(Sheets(sh).Range(TestRange2))) = UCase(Trim(Sheets(destsh).Range(output(1)))) And UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) Then
reportlocation = "A" + m 'partnumber
Sheets(sh).Range(TestRange2).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "B" + m 'ID number
Sheets(destsh).Range(reportlocation) = TestRange3
reportlocation = "C" + m 'X where the part was scanned
Sheets(destsh).Range(reportlocation) = strvar1

'End If
ElseIf UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(5)))) Then 'ROLL
p = p + 1
m = p + 1
'If UCase(Trim(Sheets(sh).Range(TestRange2))) = UCase(Trim(Sheets(destsh).Range(output(1)))) And UCase(Trim(Sheets(sh).Range(TestRange3))) = UCase(Trim(Sheets(destsh).Range(output(13)))) Then
reportlocation = "A" + m 'partnumber
Sheets(sh).Range(TestRange2).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "B" + m 'ID number
Sheets(sh).Range(TestRange3).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "E" + m
Sheets(destsh).Range(reportlocation) = strvar1

'End If
ElseIf UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(7)))) Then 'HT
p = p + 1
m = p + 1
'If UCase(Trim(Sheets(sh).Range(TestRange2))) = UCase(Trim(Sheets(destsh).Range(output(1)))) Then
reportlocation = "A" + m 'partnumber
Sheets(sh).Range(TestRange2).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "B" + m 'ID number
Sheets(sh).Range(TestRange3).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "F" + m
Sheets(destsh).Range(reportlocation) = strvar1

' End If
ElseIf UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(9)))) Then 't/p
p = p + 1
m = p + 1
'If UCase(Trim(Sheets(sh).Range(TestRange2))) = UCase(Trim(Sheets(destsh).Range(output(1)))) Then
reportlocation = "A" + m 'partnumber
Sheets(sh).Range(TestRange2).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "B" + m 'ID number
Sheets(sh).Range(TestRange3).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "D" + m
Sheets(destsh).Range(reportlocation) = strvar1

'End If
ElseIf UCase(Trim(Sheets(sh).Range(TestRange))) = UCase(Trim(Sheets(destsh).Range(output(11)))) Then 'ship
p = p + 1
m = p + 1
'If UCase(Trim(Sheets(sh).Range(TestRange2))) = UCase(Trim(Sheets(destsh).Range(output(1)))) Then
reportlocation = "A" + m 'partnumber
Sheets(sh).Range(TestRange2).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "B" + m 'ID number
Sheets(sh).Range(TestRange3).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "G" + m
Sheets(destsh).Range(reportlocation) = strvar1

'End If
End If
n = n + 1
TestRange = "E" + n
TestRange2 = "B" + n
TestRange3 = "F" + n
output(1) = "A" + n
output(13) = "B" + n
Next
End Sub

 

[wafs]

OH yea, it is in EXCEL.

 

[xlbo]

wow - that's a lot of code to post - can you not just post the relevant bit?

Also, what in the process tells you which column to put the x in ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wafs]

reportlocation = "C" + m 'put X where the part was scanned


reportlocation tells which column to put the post in, so there is one for each column