Advanced String Manipulation

Help
1

Every day I want to change the range of an area of an excel spreadsheet by adding 1 row to the top and bottom of the range. The range as a string is balances!$A$105:$M$107. I can get that into a string variable of course pretty easily but I’m not finding doc’s on string manipulation. I need to find the 105 and 107 and increment each by 1. Of course those could be any number. every year they will go up by about 260 (days of the week). One thing to note, while not adding the the robust nature of a solution in this particular case the position of the number will be fixed at 13,3 and 21,3 for nearly 4 years.

2

Hi, not completely sure what you need. But I can help with incrementing the row number.

“balances!$A$”+Cstr(numberVariable)+“:$M$”+Cstr(numberVariable+2)

Then, just increment numberVariable.

Thanks.

1 Like
3

Thank you. I was being brain dead this morning. Another cup of coffee and it came to me. For anyone who runs into this an answer is use assign and convert.ToInt32 and .SubString. I have to go to another meeting but I’ll post the whole answer here when I have it debugged as one assign statement.
the original string includes a = sign from the range text box in the name manager

here is the conversion to add 1 row to the range. The point of all this is to get the balances on several accounts daily and add a row with formulas at the bottom for each day added.

string: =balances!$A$105:$M$107

footerRangeOriginal.SubString(13,3))+1).ToString+footerRangeOriginal.Substring(16,4)+(convert.ToInt32(footerRangeOriginal.Substring(20,3))+1).ToString

I’m a big fan of peer review so any better solutions to this problem are more than welcome. Right now my workflow is basically:
Excel Application scope
read range sheet “balances” range “footer”
click “formulas tab”
click “Name Manager”
there is only one named range so it’s selected already
get text: from the refers to: text box
(the assignment from above)
type into: the refers to text box the newly created footer range value
then I’ll write the datatable from step 1 into the new range into the new range

1 Like