Help with looping - probably simple!

[dullboy]

Hi guys,

Ive got a delimited text file which contains several columns, one of which is product code. The product codes are old and need to be changed to the new product codes and file saved as tab delimited before i send the file onto the customer.

I have created an excel sheet (table.xls)which contains all the old product codes in A1-A200 and the new ones next to them in B1-B200.

I have started writing the macro and opened the delimited file into excel and opened up the table.xls. I need to loop through column D of the txt file and find the corresponding entry within A1-A200 then take the value from the next column over and put it into col D of the text file. If the code is not in the range it would put a default value there.

Any ideas please ??

Thanks in advance,

 

[DonkeyOte]

change the first if to

If LDATA <> CODE AND CROW <> 4 Then CROW = CROW + 1


and then put the END IF after:

IF LDATE <> CODE AND CROW = 4 THEN

Workbooks(2).Worksheets(1).Activate
RANGE("D" & PROW) = "NO CODE"


If this still doesn't work then we can put in a GoTo as part of the above if - i.e skip the final if and jump to next code...

 

[dullboy]

yeah it doesnt work - seems to be setting them all to default value apart from the one that should be set to default value! Here it is as it looks at the mo :-

For Each LDATA In DATA
If LDATA = "" Then Exit For
If LDATA <> "" Then
For Each CODE In Data2
Workbooks(3).Worksheets(1).Activate
If LDATA <> CODE And CROW <> 4 Then CROW = CROW + 1
If LDATA = CODE Then
NEWCODE = Range("B" & CROW)
Workbooks(2).Worksheets(1).Activate
Range("D" & PROW) = NEWCODE
End If
If LDATA <> CODE And CROW = 4 Then
Workbooks(2).Worksheets(1).Activate
Range("D" & PROW) = "P00470"
End If
Next CODE
CROW = 1
End If
PROW = PROW + 1
Next LDATA

Cheers again,

 

[DonkeyOte]

Try changing the order...

Also, recommend leaving blanks between key lines of code etc as this makes it simpler to read...

Sorry about this...hard to do without seeing the data in front of you...

For Each LDATA In DATA

If LDATA = "" Then Exit For

If LDATA <> "" Then

For Each CODE In Data2

Workbooks(3).Worksheets(1).Activate

If LDATA <> CODE And CROW = 4 Then
Workbooks(2).Worksheets(1).Activate
Range("D" & PROW) = "P00470"
End If

If LDATA = CODE Then
NEWCODE = Range("B" & CROW)
Workbooks(2).Worksheets(1).Activate
Range("D" & PROW) = NEWCODE
End If

If LDATA <> CODE And CROW <> 4 Then CROW = CROW + 1

Next CODE

CROW = 1

End If

PROW = PROW + 1

Next LDATA

 

[dullboy]

Mate,

That is awesome!! Working like a charm!

Thanks very much.

 

[DonkeyOte]

glad to have helped...keeps the grey matter in check.