I have imported txt files into 18 workshets in an Excel file that need
to have the value in column E changed from text data to a number.
They're actually accout names that I want to have replaced with an
account number. The Macro should go through all of the sheets in the
Excel file (right now that's 18) and replace all occurences of
"PURCHASES" in column E with "51000", replace all occurences of
"ADVERTISING" in column E with "63100" and replace all occurences of
"ALBERT" in column D with "65010". This would get me started enough
that I think I could code the rest of the macro, I just need to know
how to get started. I would like this macro to be saved in my
Personl.xls file so I can use it on multiple worksheets, but at this
time I have all of these worksheets combiled as one in order to test
it on all of them to make sure it will work before I use it on my
"live" data that I will import into a client's accounting software.

If there's some help out there, it would be great.

Thanks. You're in my prayers.

Josh

Sub MakeReplacements()
dim varr as variant, varr1 as variant, i as long
Dim sh as Worksheet, varr2 as variant
varr = Array("advertising", "purchases","albert")
varr1 = Array(63100, 51000, 65010)
varr2 = Array(5,5,4)
For each sh in Worksheets
for i = lbound(varr) to ubound(varr)
Columns(varr2(i)).Replace What:=varr(i), Replacement:=varr1(i), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next i
Next sh
End Sub

--
Regards,
Tom Ogilvy



"Josh" wrote in message
om...
I have imported txt files into 18 workshets in an Excel file that need
to have the value in column E changed from text data to a number.
They're actually accout names that I want to have replaced with an
account number. The Macro should go through all of the sheets in the
Excel file (right now that's 18) and replace all occurences of
"PURCHASES" in column E with "51000", replace all occurences of
"ADVERTISING" in column E with "63100" and replace all occurences of
"ALBERT" in column D with "65010". This would get me started enough
that I think I could code the rest of the macro, I just need to know
how to get started. I would like this macro to be saved in my
Personl.xls file so I can use it on multiple worksheets, but at this
time I have all of these worksheets combiled as one in order to test
it on all of them to make sure it will work before I use it on my
"live" data that I will import into a client's accounting software.

If there's some help out there, it would be great.

Thanks. You're in my prayers.

Josh

I altered the macro a little because the assertion

"varr2 = Array(5,5,4)"

doesn't look to the 4th column and change the 5th column data. What I
have is this:

Sub AccountNameToNumber()
Dim varr As Variant, varr1 As Variant, i As Long
Dim sh As Worksheet, varr2 As Variant
varr = Array("advertising", "purchases", "bait", "insurance", "payroll
liabilities", "automobile expense", "rent")
varr1 = Array(63000, 51000, 51000, 65200, 65500, 65100, 64400)
varr2 = Array(5, 5, 5, 5, 5, 5, 5)
For Each sh In Worksheets
For i = LBound(varr) To UBound(varr)
Columns(varr2(i)).Replace What:=varr(i), Replacement:=varr1(i), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next i
Next sh
End Sub

and it is good at changing the data in the 5th column with the values
(account numbers)listed in the varr1 line. I sometimes need to look
at partial data in the 4th column to make a change the data in the 5th
column to values (account numbers). For instance, if the 4th column
reads "AMERICAN EXPRESS" I want to be able to look for the data
"express" and have the data in the 5th column change to a value
specified appropraitely. Any ideas?

Thanks for helping out,

Josh


"Tom Ogilvy" wrote in message ...
Sub MakeReplacements()
dim varr as variant, varr1 as variant, i as long
Dim sh as Worksheet, varr2 as variant
varr = Array("advertising", "purchases","albert")
varr1 = Array(63100, 51000, 65010)
varr2 = Array(5,5,4)
For each sh in Worksheets
for i = lbound(varr) to ubound(varr)
Columns(varr2(i)).Replace What:=varr(i), Replacement:=varr1(i), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next i
Next sh
End Sub

--
Regards,
Tom Ogilvy



"Josh" wrote in message
om...
I have imported txt files into 18 workshets in an Excel file that need
to have the value in column E changed from text data to a number.
They're actually accout names that I want to have replaced with an
account number. The Macro should go through all of the sheets in the
Excel file (right now that's 18) and replace all occurences of
"PURCHASES" in column E with "51000", replace all occurences of
"ADVERTISING" in column E with "63100" and replace all occurences of
"ALBERT" in column D with "65010". This would get me started enough
that I think I could code the rest of the macro, I just need to know
how to get started. I would like this macro to be saved in my
Personl.xls file so I can use it on multiple worksheets, but at this
time I have all of these worksheets combiled as one in order to test
it on all of them to make sure it will work before I use it on my
"live" data that I will import into a client's accounting software.

If there's some help out there, it would be great.

Thanks. You're in my prayers.

Josh