Excel VBA code; Create formula using variable as sheet name and defined cell range

[DPenfold]

Having little trouble with this coding, I'm doing I can get it working, slightly, however it brought a big flaw and google isn't much;

 

Here is an example;

ActiveCell.FormulaR1C1 = "='" & test & "'!R[-1]C"

( Test = (Variable) referencing Sheet name )

 

The Result would be (In Cell A3) "=Sheet1!A2" Which for the first 30 cells (A3-A32) is great, however when I come to next 30 cells A33-A62 (and so on etc.) I want it to 'restart' so the result would be (in A33) "=Sheet2!A2" however with the current line of code it comes up as (in A33) "=Sheet2!A32" which references nothing.

 

I've tried manually entering the the cell reference (in to a combined string variable) and predefined ranges as variable but sadly vba doesn't like it.

 

I need it to show "=Sheet1!A2" so using stuff like indirect doesn't really help as later on in coding I have format changes etc.

 

If anyone can shed some light on this that would be great, or if I not explained it well above (in a rush) let me know an I'll rewrite it .

 

Thanks in advance

[Steve21]

You got an example workbook with VBA etc Easier to do than starting from scratch

 

Steve

[pcstru]

The fact that you need to reset after 30 cells suggests you need to keep track of that and derive the reference accordingly. the Mod operator is your friend for doing that, so if we count with iIdx from 0..n but we want it to spit out sequences of 0..29, it would be refno = i mod 30 which can then be concatenated into the string used as the cell formula.

[DPenfold]

Sorry for the late reply

 

The fact that you need to reset after 30 cells suggests you need to keep track of that and derive the reference accordingly. the Mod operator is your friend for doing that, so if we count with iIdx from 0..n but we want it to spit out sequences of 0..29, it would be refno = i mod 30 which can then be concatenated into the string used as the cell formula.

 

Not used Mod operator before nor refno so I'll have to look into it, thank you

 

You got an example workbook with VBA etc Easier to do than starting from scratch

 

Steve

 

I'll will attach now testing - inputbox.xlsm(remove me and .txt).txt just remove the brackets and '.txt' extension as forum didn't like the marco format of the excel file any input would be appreciated as all knowledge i learn from these excel projects have comes in handy when i use them for work etc.

 

If you're busy and/or unable then don't worry

[pcstru]

Not used Mod operator before nor refno so I'll have to look into it, thank you

In my (not very clear) reply, refno was just a variable. Mod is the remainder from an integer division operation i.e. 10 divided by 3 = 3 remainder 1. So 10 Mod 3 = 1. It is extremely useful in programming, especially for when you need to do something every n things (every 30 cells, every 10 cakes, every 22 dead aliens etc).

[DPenfold]

In my (not very clear) reply, refno was just a variable. Mod is the remainder from an integer division operation i.e. 10 divided by 3 = 3 remainder 1. So 10 Mod 3 = 1. It is extremely useful in programming, especially for when you need to do something every n things (every 30 cells, every 10 cakes, every 22 dead aliens etc).

 

Think I'm little more confused by that (could be the fact I that I end up doing all my maths calculations via excel lol) I'll have a think about it and see what i can do as I thought about doing equations to predict the cell reference etc. but thought the should have been an easier way doing so.

 

Keep you updated if I have a solution with your suggestion thanks either way .

@Steve21 After replying and uploading the excel I'm little worried you may be little confused with what i'm trying to achieve (more so with the coding issue detailed here) etc. so let me know if it's not straight forward and need me pointing you to the right location .

[DPenfold]

ActiveCell.FormulaR1C1 = "='" & test & "'!R[-1]C"

( Test = (Variable) referencing Sheet name )

 

Solved my problem xD Above is the code that worked until the next 30 set of cells then it would start causing issues.

 

Simple fix, remove 'R1C1' from the formula command (if that the right way of labeling it lol) not it will work as the following;

 

ActiveCell.Formula = "='" & test & "'!A2"

 

Works pretty much how I want/need it

 

Thanks to all who pitched in ideas worth while to test them out as they may come in handy in the future