macro to find and replace within VBA code

[surlybiker]

I'm not even sure if this is possible, but here goes.

Here is my particular need in a nutshell: Is there a way for a macro to look at a worksheet (entitled "Sheet1" which is the first sheet in a workbook entitled "Sheet_1.xls"), find the last row of data, and then replace three particular numbers in the VBA code of another workbook (entitled "Sample.xlsm") with the sum of that "last row of data" number and 17.

An example: if the last row of data on Sheet1 is 350, the macro would look within existing macro code (of the Sample.xlsm workbook) and find the numbers 4997, 4998, and 5008 and replace them with 367, 368, and 378, respectively. If the last row was 3,100, it would find and replace those numbers (4997, 4998, and 5008) with 3117, 3118, and 3128.

It is likely that, being an amateur, my existing code is extremely poorly written. However, in the event my request is even possible, I thought I'd throw it out there.

Thanks so much for the help!

 

[GlennUK]

Explain the logic behind the request. Why do you want to change the values in the macro code .... for what purpose?

 

[surlybiker]

Thanks for the reply. I'll do my best to explain, as I came on this project late. My coworkers and I are not highly skilled in Excel, but know just enough to create some rudimentary macro code through searching forums and recording sequences using the macro recorder.

In the macro code for the "Sample.xlsm" document, there is code that looks something like this:
ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[4998]C)";

and this:
ActiveCell.FormulaR1C1 = "=SUM(R[2]C[-11]:R[4997]C[-1])/R[-1]C";

and finally this:
ActiveCell.FormulaR1C1 = "=SUM(RC[-30]:RC[-1])"
Selection.AutoFill Destination:=Range("BTW13:BTW5008"), Type:=xlFillDefault.

These are only a few examples of the sections in question. Once again, I'm wondering if there is a way to better automate the code so it knows how many rows to include in formulas like those mentioned above. The final row to be included in the formula differs based on the cell in which it is located (e.g., row 10 or row 13), with the idea that the formula would simply account for all rows of data.

I can leave the code as it stands and it works fine, it just takes a bit longer when there are few rows of data (e.g., 200 versus 3700). Our knowledge of VBA is obviously limited, and our existing code is likely quite clunky. I'm just trying to clean it up a bit, without having to redo everything.

Thanks again for the help.

 

[GlennUK]

I should think that the last row of data is easy to calculate, if you know which column to examine. For example, if column A has data in it, then the last row would be:

iLastRow=Range("A" & Rows.Count).End(xlUp).Row

then you'd use iLastRow in your code instead of 4998 or 5008 or whatever. Like:

ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[" & iLastRow & "]C)"

or

 Selection.AutoFill Destination:=Range("BTW13:BTW" & iLastRow ), Type:=xlFillDefault

The code would then work for all data, and you don't have to do any weird macro tweaking.

 

[surlybiker]

Oh, wow, this is brilliant! I think it will work flawlessly with the code enclosed in brackets (i.e., replacing 4997 and 4998), but when I need to replace 5008, I get a syntax error message on lines such as the following:

Range("GV13:GV" & iLastRow & ").Select

as well as:

Selection.AutoFill Destination:=Range("EX13:EX" & iLastRow & "), Type:=xlFillDefault

I've tried several permutations of the code you provided but cannot quite make it work. Again, it is only regarding replacing the occurrences of "5008".

Any thoughts?

Many thanks for the help!

 

[Rick Rothstein]

I get a syntax error message on lines such as the following:

Range("GV13:GV" & iLastRow & ").Select

as well as:

Selection.AutoFill Destination:=Range("EX13:EX" & iLastRow & "), Type:=xlFillDefault

The error is due to your quote marks not being balanced (you have an odd number of them). I am not sure why you think you need that & " in there. Try these and see if they do what you want...

Range("GV13:GV" & iLastRow).Select
 
Selection.AutoFill Destination:=Range("EX13:EX" & iLastRow), Type:=xlFillDefault

 

[surlybiker]

Thanks for the prompt reply. I tried your suggestion, but unfortunately I get this error on those lines of code (i.e., the ones in which I'm replacing "5008"):

Run-time error '1004':
Method 'Range' of object '_Global' failed

Any thoughts?

Thanks again for the reply.

 

[GlennUK]

Thanks for the prompt reply. I tried your suggestion, but unfortunately I get this error on those lines of code (i.e., the ones in which I'm replacing "5008"):

Run-time error '1004':
Method 'Range' of object '_Global' failed

Any thoughts?

Thanks again for the reply.

Any thoughts? Yeah, how about showing the code that is failing?

 

[surlybiker]

Here are two sections of code that are troublesome (i.e., that involve "iLastRow"...I provided other lines as context):

Range("EX13").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-30]:RC[-1])"
Selection.AutoFill Destination:=Range("EX13:EX" & iLastRow), Type:=xlFillDefault

Range("GV13:GV" & iLastRow).Select
Selection.Replace What:="*", Replacement:="State Team Meeting"

Please let me know if further information would be useful, and thanks again for the assistance!

 

[GlennUK]

Hi, there's nothing wrong with the autofill code ... as long as iLastRow has a meaningful content .... when it stops there, do you go into debug mode and look at what iLastRow is? ( hover the mouse over it, and a tooltip should show it's value .... or View the Immediate Window, and type "? iLastRow" without the quotes )