Something like this could serve as a general-purpose "find column" function:


Function GetCol(sVal as string) as long
dim f as range
Set f = Rows(1).Find(what:=sVal, LookIn:=xlValues)

if not f is nothing then
GetCol=f.column
else
GetCol=0
end if

end function



wrote in message
...
Hey all,

I am currently trying to optimize a set of code i wrote which creates
new spreadsheets, and fills their cells with data and equations pulled
from other source sheets. It is a very long set of code (some 869
lines) with alot of repeating cell-by-cell operations. Is there a way
to make the whole thing recursive, and possibly use objects instead of
lots of copied/pasted repeated code using nothing but simple
variables. If more detail or a sample of the code is necessary, i can
provide.

For example, part of my code searches for the location of a named cell
(column header), and assigns a variable its column position to use
later on with copy/paste operations when the new spreadsheet is
generated. This code is repeated MANY times, with different variable
names being subbed in....Any way to create and object out of this
process and just import the new variable names and set them in a
recursive way, instead of just duplicating this code:

Set Rcc = Rows(1).Find(what:="Rcc", LookIn:=xlValues)
If Not Rcc Is Nothing Then
myaddress = Mid(Rcc.Address, 2)
Rcccol = Left(myaddress, InStr(myaddress, "$") - 1)
End If

Any ideas or suggestions would be more than welcome. Thanks!

-Pogster