Tick-Tock said:Here is the formula and perhaps i should have said string
if VBA treats strings as an array of characters like other
languages.
=+'R:\INCAUDIT\[IJ090103.XLS]REPORT'!$G$122
Every month I need to go in and change the source
spreadsheets name to the nexts months dates as they follow
this format IJMMDDYY and it has become an unbearable task
to change one number in this formula 527 times (17 Credit
card types X 31 days).
So I want to read the formula into an array and when the
macro is done this:
=+'R:\INCAUDIT\[IJ090103.XLS]REPORT'!$G$122
would become
=+'R:\INCAUDIT\[IJ100103.XLS]REPORT'!$G$122
I just dont know enough about working with arrays and such
in Excel. The most i could figure out is how to get the
formula into a variable and back into the cell but
changing the formula is the hard part.
MC1 = Range("A2").Formula
" Code to change the formula. "
" I know the code will be wrong but"
" something along the lines of:"
TEMP[0] = MC1[19] "Grab the 19 char into temp VAR"
TEMP[1] = MC1[20] "Grab the 20 char into temp VAR"
REDIM TEMP = intager "Redefine the array as an integer"
TEMP = TEMP + 1 "Incriment the integer"
REDIM TEMP[2] "Redefine the inrager as an array"
MC1[19] = TEMP[0] "Drip the new number in the formula"
MC1[20] = TEMP[1] "Drip the new number in the formula"
Range("A2").Formula = MC1
Again any help would be appreciated.
Tick-Tock
-----Original Message-----
Why are you reading a single cell/formula into an Array. If it is a formula
like =Sum(A1:B9) then what do you mean by increment by 1 value. Do you
mean increase the range by one cell?
--
Regards,
Tom Ogilvy
.
Tick-Tock said:That is a really good idea i was looking to make it
entirely more complicated then I think i need to. Thanks
for your help
-----Original Message-----
Can't tell how you are using this in code - so I'll just guess and maybe
give
you a hint of how to use it
Dim mydate as String
mydate = InputBox("Enter new date","New Date")
' or
mydate = Sheets("Sheet1").Range("A1")
Sheets("Sheet1").Range("A1").Formula = _
"=+'R:\INCAUDIT\[IJ" & mydate & ".XLS]REPORT'!$G$122"
[also - don't think you need the "+" after the "=". looks like a carry over
from Lotus]
--
sb
Tick-Tock said:Here is the formula and perhaps i should have said string
if VBA treats strings as an array of characters like other
languages.
=+'R:\INCAUDIT\[IJ090103.XLS]REPORT'!$G$122
Every month I need to go in and change the source
spreadsheets name to the nexts months dates as they follow
this format IJMMDDYY and it has become an unbearable task
to change one number in this formula 527 times (17 Credit
card types X 31 days).
So I want to read the formula into an array and when the
macro is done this:
=+'R:\INCAUDIT\[IJ090103.XLS]REPORT'!$G$122
would become
=+'R:\INCAUDIT\[IJ100103.XLS]REPORT'!$G$122
I just dont know enough about working with arrays and such
in Excel. The most i could figure out is how to get the
formula into a variable and back into the cell but
changing the formula is the hard part.
MC1 = Range("A2").Formula
" Code to change the formula. "
" I know the code will be wrong but"
" something along the lines of:"
TEMP[0] = MC1[19] "Grab the 19 char into temp VAR"
TEMP[1] = MC1[20] "Grab the 20 char into temp VAR"
REDIM TEMP = intager "Redefine the array as an integer"
TEMP = TEMP + 1 "Incriment the integer"
REDIM TEMP[2] "Redefine the inrager as an array"
MC1[19] = TEMP[0] "Drip the new number in the formula"
MC1[20] = TEMP[1] "Drip the new number in the formula"
Range("A2").Formula = MC1
Again any help would be appreciated.
Tick-Tock
-----Original Message-----
Why are you reading a single cell/formula into an Array.
If it is a formula
like =Sum(A1:B9) then what do you mean by increment by
1 value. Do you
mean increase the range by one cell?
--
Regards,
Tom Ogilvy
Ok so I'm trying to read a formula into an array and
increment 1 value in the array then dump that formula
back
into the same sell... any help would bre great.
Tick
.
.