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

I have no idea how you would use recursive code here but a simple functoin
would possibly help for what you posted...

public Function GetCol(byval rngRow as range, byval strValue as string) as
long
dim rngFound as range

set rngfound = rngrow.find(What:=strvalue,
LookAt:=xlWhole, LookIn:=xlValue, MatchCase:=False)
if rngfound is nothing then
GetCol = -1
else
getcol = rngfound.column
end if
end function

Now when you need to find a column just call this function suppling the
Range to be searched and the value to be located.
--
HTH...

Jim Thomlinson


" wrote:

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

Thats a great start. Thanks for that code suggestion Jim. Maybe
recursive is not the right word, its just that alot of this macro is
the same exact code copied over and over, with slight changes in
variable names, or cell positions. Isnt there a way to shorten the
code by using loops, or objects, or something besides simple
variables.
I might not be savvy enough in VBA to figure out that code on my own,
but i know it must exist.

As for your function, thats exactly what i am talking about. I guess i
meant functions, and not "objects", im thinking in terms of Java i
guess.
I will try to implement that and see if it simplifies (SHORTENS!!!) my
code at all.

The only reason i would want to shorten it, btw, is because i will be
leaving a position i now hold at a company, and i need to be able to
pass my work on to someone else, who will probably not understand VBA
very well. The less code they have to deal with, the better. That is
the atmosphere here.

Btw, what does the function call for that function look like? Is it
just Getcol(1, "Rcc") ?
The row will always be row 1, also.

Thanks again Jim.

-Pogster

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