Modules and code
 

Ok,

Very much a newbie to vb and excel macros. I have some coding on
one sheet that I would like to have applied to another sheet. I have
been told that this should be accomplished by inputing the vb code in a
module instead of the sheet. I have two sheets. The one that presently
has the code in it is called "master" the one that I want to apply the
code to is called "invoice". Finally, to my question: what needs to
be listed in the module code section and what needs to be listed in the
"master" code section to make this code work on the "invoice" sheet
only. I would put the code in the "invoice" sheet but because of some
other issues, i can't.

Following is a section of the code I want to implement:

Sub tax()
mytext = "ALMFG: AL MFG TAX"
For a = 1 To 9999
If Cells(a, "c") = mytext Then Cells(a, "q") = Cells(a, "e")
Next a
mytext = "SHMFG: SHELBY CO MFG TAX"
For s = 1 To 9999
If Cells(s, "c") = mytext Then Cells(s, "p") = Cells(s, "e")
Next s
mytext = "PEMFG: PELHAM MFG TAX"
For p = 1 To 9999
If Cells(p, "c") = mytext Then Cells(p, "n") = Cells(p, "e")
Next p
mytext = "HEMFG: HELENA MFG TAX"
For h = 1 To 9999
If Cells(h, "c") = mytext Then Cells(h, "o") = Cells(h, "e")
Next h

Thanks for any help, in advance...

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=516149

Modules and code
 

Hi,
put this a general module rather than your "master" sheet: note the
"." in front the Cells which . in cobination with the "With ...." statement,
ensure it is the cells in the "invoice" worksheet which are processed.

Sub tax()
Worksheets("Invoice").Select
With Worksheets("Invoice")
mytext = "ALMFG: AL MFG TAX"
For a = 1 To 9999
If .Cells(a, "c") = mytext Then .Cells(a, "q") = .Cells(a, "e")
Next a
mytext = "SHMFG: SHELBY CO MFG TAX"
For s = 1 To 9999
If .Cells(s, "c") = mytext Then .Cells(s, "p") = .Cells(s, "e")
Next s
mytext = "PEMFG: PELHAM MFG TAX"
For p = 1 To 9999
If .Cells(p, "c") = mytext Then .Cells(p, "n") = .Cells(p, "e")
Next p
mytext = "HEMFG: HELENA MFG TAX"
For h = 1 To 9999
If .Cells(h, "c") = mytext Then .Cells(h, "o") = .Cells(h, "e")
Next h
End With


"Metrazal" wrote:


Ok,

Very much a newbie to vb and excel macros. I have some coding on
one sheet that I would like to have applied to another sheet. I have
been told that this should be accomplished by inputing the vb code in a
module instead of the sheet. I have two sheets. The one that presently
has the code in it is called "master" the one that I want to apply the
code to is called "invoice". Finally, to my question: what needs to
be listed in the module code section and what needs to be listed in the
"master" code section to make this code work on the "invoice" sheet
only. I would put the code in the "invoice" sheet but because of some
other issues, i can't.

Following is a section of the code I want to implement:

Sub tax()
mytext = "ALMFG: AL MFG TAX"
For a = 1 To 9999
If Cells(a, "c") = mytext Then Cells(a, "q") = Cells(a, "e")
Next a
mytext = "SHMFG: SHELBY CO MFG TAX"
For s = 1 To 9999
If Cells(s, "c") = mytext Then Cells(s, "p") = Cells(s, "e")
Next s
mytext = "PEMFG: PELHAM MFG TAX"
For p = 1 To 9999
If Cells(p, "c") = mytext Then Cells(p, "n") = Cells(p, "e")
Next p
mytext = "HEMFG: HELENA MFG TAX"
For h = 1 To 9999
If Cells(h, "c") = mytext Then Cells(h, "o") = Cells(h, "e")
Next h

Thanks for any help, in advance...

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=516149

Modules and code
 

Works Great...
Thanks


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=516149