Can't Remember what the code does

[stephicohu]

Hi guys!

I have a simple question to ask. I got this code from this message board last year. The question is I don't remember what this code does?

The code is the following:


StrFormula = "= IFERROR('" & SourceMonth & "'!R" & FirstRow1 & "C" & SourceColumn & ",0)"
Sheets(TargetMonth).Cells(TargetRow, TargetColumn).Formula = StrFormula

Sheets(TargetMonth).Activate

I am guessing that StrFormula lays out the operand of IFERROR and is taking the sourcemonth data and moving it to the targetmonth. What I don't know is what R does or C. Can someone help me to understand this code?

I would appreciate this

Stephanie Cohu

 

[myall_blues]

If your workbook is set to use R1C1 reference styles (Options->Formulas->R1C1 reference style) or it was set by the VBA code, then you can refer to cells in a Row/Column format instead of using letters and numbers. So R5C2 refers to cell B5.

Your code is defining a formula using that format and then putting it in a cell.

 

[StephenCrump]

The code posted won't do much by itself. It relies on a number of variables that presumably have been populated previously.

Purely for illustration purposes, let's assume they have the following values:

'Assumes you have worksheets called "Jul 2024" and "Aug 2024"
SourceMonth = "Jul 2024"
TargetMonth = "Aug 2024"

'Arbitrary values, just to ilustrate
FirstRow1 = 5
SourceColumn = 7
TargetRow = 11
TargetColumn = 13

Then this line:

StrFormula = "=IFERROR('" & SourceMonth & "'!R" & FirstRow1 & "C" & SourceColumn & ",0)"

will put the string value: "= IFERROR('Jul 2024'!R5C7,0)" into StrFormula, so that this line

Sheets(TargetMonth).Cells(TargetRow, TargetColumn).Formula = StrFormula

puts that formula into 'Aug 2024'!M11 (as M11 is row 11, column 13)

There are two ways of referencing cells. Generally we use "A1" referencing, but occasionally it's helpful to use "R1C1" referencing, where R1C1 means row 1, column 1, i.e. cell A1.

R5C7 refers to row 5, column 7, i.e. G5, so the end result is that
'Aug 2024'!M11 now contains the formula: = IFERROR('Jul 2024'!$G$5,0)

 

[stephicohu]

The code posted won't do much by itself. It relies on a number of variables that presumably have been populated previously.

Purely for illustration purposes, let's assume they have the following values:

'Assumes you have worksheets called "Jul 2024" and "Aug 2024"
SourceMonth = "Jul 2024"
TargetMonth = "Aug 2024"

'Arbitrary values, just to ilustrate
FirstRow1 = 5
SourceColumn = 7
TargetRow = 11
TargetColumn = 13

Then this line:

StrFormula = "=IFERROR('" & SourceMonth & "'!R" & FirstRow1 & "C" & SourceColumn & ",0)"

will put the string value: "= IFERROR('Jul 2024'!R5C7,0)" into StrFormula, so that this line

Sheets(TargetMonth).Cells(TargetRow, TargetColumn).Formula = StrFormula

puts that formula into 'Aug 2024'!M11 (as M11 is row 11, column 13)

There are two ways of referencing cells. Generally we use "A1" referencing, but occasionally it's helpful to use "R1C1" referencing, where R1C1 means row 1, column 1, i.e. cell A1.

R5C7 refers to row 5, column 7, i.e. G5, so the end result is that
'Aug 2024'!M11 now contains the formula: = IFERROR('Jul 2024'!$G$5,0)

Yes, you are right the arbitrary values are given earlier in the code. I just copy the code I had questions on. BTW, thank you for your clarifying answer and your quick response. This is a way you don't have to use the Copy, Paste Link formula.

Stephanie Cohu

 

[stephicohu]

Hi Stephen!

I tried the code and I think I need another set of eyes to view it.

I am using the same code that you help me with earlier. The code is shown below:

         strformula = "= IFERROR(' " & ssourcemonth & " ' !R" & isourcerow1 & "C" & isourcecol2 & ",0)"
         Sheets(stargetmonth).Cells(itargetrow1, itargetcol).Formula = strformula

Where:
ssourcemonth = "Sheet 1"
stargetmonth = "Sheet 3"
isourcerow1 = 9
isourcecol2 = 34
itargetrow1 = 9
itargetcol = 2

on the 2nd line of this code I get the following error:

Run-Time error '1004'
Application-defined or object-defined error.

I ran the code and everything works out fine except for this point in the code. Did I miss something?

 

[Peter_SSs]

You have a number of stray space characters in the strformula line this time. Try this instead.

strformula = "=IFERROR('" & ssourcemonth & "'!R" & isourcerow1 & "C" & isourcecol2 & ",0)"

 

[stephicohu]

Good morning all:

I have another question this time the compiler is stating cannot find "Sheet1." Copy form: The error is on top of the window showing the open directory looking for Sheet1. I have Sheet1 as a sheet in the present workbook. I have interchange the names thinking this was the problem. It proved not to be the answer. The vba code is:

         Sheets(ssourcemonth).Select
         itargetrow1 = 9
         isourcerow1 = 9

         strformula = "=IFERROR(' " & ssourcemonth & " '!R" & isourcerow1 & "C" & isourcecol2 & ",0)"
         Sheets(stargetmonth).Cells(itargetrow1, itargetcol).Formula = strformula

it happens on the next line. I have change the sheet names, remove sheets (ssourcemonth), added sheets(targetmonth). acitvate and it still gives this problem.

Sheets(stargetmonth).Cells(itargetrow1, itargetcol).Formula = strformula [/CODE]

Where:
ssourcemonth = "Sheet 1"
stargetmonth = "Sheet 3"
isourcerow1 = 9
isourcecol2 = 34
itargetrow1 = 9
itargetcol = 2

What am i doing wrong?[ATTACH type="full"]114669[/ATTACH]

 

[MARK858]

Are the spaces between Sheet and the number supposed to be there?

 

[stephicohu]

Yes, it is correct. VBA complier doesn't allow a space between sheets and the parenthesis. sheets () doesn't work.

 

[stephicohu]

Okay, I looked over the code below

strformula = "=IFERROR(' " & ssourcemonth & " '!R" & isourcerow1 & "C" & isourcecol2 & ",0)"

took out a couple more spaces and the software works...... Sorry about that guys. I do appreciate the work you guys do!