Help with looping - probably simple!

dullboy

New Member
Joined
Aug 29, 2002
Messages
22
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,
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

zzydhf

Board Regular
Joined
Apr 10, 2002
Messages
236
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

Well-known Member
Joined
Sep 3, 2002
Messages
675
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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

New Member
Joined
Aug 29, 2002
Messages
22
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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

New Member
Joined
Aug 29, 2002
Messages
22
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Aug 29, 2002
Messages
22
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top