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,

 

[zzydhf]

you could put a Vlookup in column E of the table, then copy paste values over column D, then delete column E

e.g.

Range("E1").Select
ActiveCell.FormulaR1C1 = "Your VLOOKUP Here"
Range("E1").Select
Selection.Copy
Range("E1:E200").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("E1:E200").Select
Selection.ClearContents


*** probably easier to do manually if a onetime deal...
_________________
Oh, you hate your job? Why didn't you say so? There's a support group
for that. It's called EVERYBODY, and they meet at the bar. - Drew Carey
This message was edited by zzydhf on 2002-10-11 07:29
This message was edited by zzydhf on 2002-10-11 07:30

 

[ChrisUK]

You need to use a lookup value in the worksheet, someting like

=VLOOKUP("Code2",table,2,FALSE)

In place of code 2 you can put a cell reference

Hope this helps

 

[Andrew Poulsom]

You can do this without a macro.

In a spare column in your imported text file enter a formula to look up the new code and copy it down. Select all the entries in the column and choose Edit Copy. Select the first cell in the old column and choose Edit Paste Special Values. Then delete the new column and resave the text file.

 

[DonkeyOte]

If you are going to have masses of lines in your txt file then yes a loop would be preferable to a vlookup (i.e. if a code appears more than once, and there are already 200 codes...)

Here's a rough example of a macro you could use:

Sub test()

'A.xls = your text file opened in .xls
'B.xls = your main file with the codes listed
'crow = current row being used in B.xls
'prow = paste row for A.xls

'start in A.xls

DATA = Sheets("SHEET1").Range("D1:D10000")

Windows("B.XLS").Activate
Data2 = Workbooks("B.XLS").Sheets("SHEET1").Range("A1:A200")
CROW = 1

PROW = 1

For Each LDATA In DATA

If LDATA = "" Then Exit For

If LDATA <> "" Then

For Each CODE In Data2

Windows("B.XLS").Activate

If LDATA <> CODE Then CROW = CROW + 1

If LDATA = CODE Then
NEWCODE = Range("B" & CROW)
Windows("A.XLS").Activate
Range("B" & PROW) = NEWCODE
End If

Next CODE

CROW = 1

End If

PROW = PROW + 1

Next LDATA

End Sub

 

[dullboy]

Cheers for the replies.

Lasw10,

Your right there are going to be hundreds of lines in these text files. Also this is a part of an automated process too, so it automatically opens, formats and saves the files. Another script then FTP's and deletes them. Ive made it so my macro's run only when txt files are FTP'd down into a designated folder. If the folder is empty nothing happens that day.

Anyway, question about your solution. What is LDATA ? Ive tried implementing your code and LDATA is always blank so it bombs out first go ?

Cheers,

 

[DonkeyOte]

if you expect lines to be blank but they aren't a problem then simply amend the line:

IF LDATA = "" THEN EXIT FOR

Because if the code is blank you will get an error when trying to find the new one...So change it to

IF LDATA = "" THEN (wherever the code was going to go) = 0

LDATA represents each cell in "DATA" - it's simply a name. So, For Each LDATA in DATA...

LDATA itself is made up, you could call it anything you like.

Cheers
Luke

_________________
LASW10
This message was edited by lasw10 on 2002-10-14 06:15

 

[dullboy]

Excellent - cheers for that. Got it working now. What Id like to add though is something that puts a default value in, if its not found on the list. Currently :-

DATA = Workbooks(2).Worksheets(1).Range("D1:D4")
Workbooks(3).Worksheets(1).Activate
Data2 = Workbooks(3).Worksheets(1).Range("A1:A4")

CROW = 1
PROW = 1

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 Then CROW = CROW + 1
If LDATA = CODE Then
NEWCODE = Range("B" & CROW)
Workbooks(2).Worksheets(1).Activate
Range("D" & PROW) = NEWCODE
End If
Next CODE
CROW = 1
End If
PROW = PROW + 1
Next LDATA

any ideas ?

Thanks in advance

 

[DonkeyOte]

Try this (quick effort) - you'll note the test is if CODE <> LDATA and CROW = max row in DATA2 (which is 4). You would need to change this as the data2 field enlarges (Assuming it works of course :wink: ).

On 2002-10-14 09:19, dullboy wrote:


DATA = Workbooks(2).Worksheets(1).Range("D1:D4")
Workbooks(3).Worksheets(1).Activate
Data2 = Workbooks(3).Worksheets(1).Range("A1:A4")

CROW = 1
PROW = 1

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 Then CROW = CROW + 1

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

IF LDATE <> CODE AND CROW = 4 THEN
Workbooks(2).Worksheets(1).Activate
RANGE("D" & PROW) = "NO CODE"

Next CODE

CROW = 1

End If

PROW = PROW + 1

Next LDATA

 

[dullboy]

cheers mate.

Getting 'Next without For' error. It doesnt like the second If statement. Cant use an End If on it because otherwise it populates them all with the default value. (exits after the first loop)

nearly there Appreciate your help so far. Can i have some more! ? lol!

Cheers,