Dynamic code with R1C1 referencing needed please!

[tungchiu]

Hi folks!
I have data in 2 rows. Row 203 contains currency values. Row 204 contains 3-digit codes starting at Col 22 and ending at Col 136 (120, 134, 200 etc).

I would like code that will search row 204 for specific codes and upon finding a given code, two things will happen.

1 A sumif formula based on values in row 203 will be inserted in the cell directly below the 3-digit code, ie, into row 205.
2 That same cell will also be defined with a range name which will include the sheet name as a part of the range name. Eg, if the sheet name = Apr-May 13, then the range name would be TotSalesApr-May 13 etc.
The code will then continue on to find the next specific code. The following is the best I can do with my limited skills in VBA. It works fine for a single instance but how do I make it dynamic.

Private Sub SumMainCodes()

sn = ActiveSheet.Name
Cells(205, 22).FormulaR1C1 = "=SUMIF(R[-1]C:R[-1]C[20], ""<=120"", R[-3]C[1]:R[-3]C[20])"
Cells(205, 22).Select
With Selection
ActiveWorkbook.Names.Add Name:="TotSales" & sn, RefersToR1C1:= _
"=" & sn & "!R205C22"
ActiveWorkbook.Names("TotSales" & sn).Comment = ""
End With
End Sub

Cheers!

 

[ZAX]

Hi folks!
I have data in 2 rows. Row 203 contains currency values. Row 204 contains 3-digit codes starting at Col 22 and ending at Col 136 (120, 134, 200 etc).

I would like code that will search row 204 for specific codes and upon finding a given code, two things will happen.

1 A sumif formula based on values in row 203 will be inserted in the cell directly below the 3-digit code, ie, into row 205.
2 That same cell will also be defined with a range name which will include the sheet name as a part of the range name. Eg, if the sheet name = Apr-May 13, then the range name would be TotSalesApr-May 13 etc.
The code will then continue on to find the next specific code. The following is the best I can do with my limited skills in VBA. It works fine for a single instance but how do I make it dynamic.

Private Sub SumMainCodes()

sn = ActiveSheet.Name
Cells(205, 22).FormulaR1C1 = "=SUMIF(R[-1]C:R[-1]C[20], ""<=120"", R[-3]C[1]:R[-3]C[20])"
Cells(205, 22).Select
With Selection
ActiveWorkbook.Names.Add Name:="TotSales" & sn, RefersToR1C1:= _
"=" & sn & "!R205C22"
ActiveWorkbook.Names("TotSales" & sn).Comment = ""
End With
End Sub

Cheers!

Here's the code (You can't name two or more cells with the same name so I added an incremental value to become like TotSalesApr-May 13 (1),TotSalesApr-May 13 (2),TotSalesApr-May 13 (3)....etc)

Private Sub SumMainCodes()
Dim Sn As String, i As Integer
Sn = ActiveSheet.Name
For i = 22 To 163
    Cells(205, i).FormulaR1C1 = "=SUMIF(R[-1]C:R[-1]C[20], ""<=120"", R[-3]C[1]:R[-3]C[20])"
    With Cells(205, i)
         ActiveWorkbook.Names.Add "TotSales" & Sn & " (" & i - 21 & ")", "=" & .Address
         ActiveWorkbook.Names("TotSales" & Sn & " (" & i - 21 & ")").Comment = ""
    End With
Next
End Sub

P.S: When posting code use code tags like [CODE] Your Code Here [/CODE]

ZAX

 

[tungchiu]

Hi Zax!
Many thanks for that! I didn’t explain myself fully but having seen the structure of your code I am thinking that perhaps what I need cannot be done easily or in a nice compact form as yours. I should also have mentioned the following.
The 3-digit code values range from 100 to 999 with no incremental pattern from one code to the next. 100, 110, 111, 112, 113, 130, 145, 150 etc
The size of the each SUMIF range is different from one range to the next.
Part of the solution will for me to review the format and layout of my spreadsheet so that data and it’s location conforms better to being manipulated in a loop. I would still appreciate your comments however.
Once again, many thanks for your help
Tungchiu

 

[ZAX]

Hi Zax!
Many thanks for that! I didn’t explain myself fully but having seen the structure of your code I am thinking that perhaps what I need cannot be done easily or in a nice compact form as yours. I should also have mentioned the following.
The 3-digit code values range from 100 to 999 with no incremental pattern from one code to the next. 100, 110, 111, 112, 113, 130, 145, 150 etc
The size of the each SUMIF range is different from one range to the next.
Part of the solution will for me to review the format and layout of my spreadsheet so that data and it’s location conforms better to being manipulated in a loop. I would still appreciate your comments however.
Once again, many thanks for your help
Tungchiu

Well I didn't understand but won't quit,Can you show me a sample data?

 

[tungchiu]

Well I didn't understand but won't quit,Can you show me a sample data?

22 23 24 25 26 27 28 29 30 31 32 33 34 35
6 Sales KPark NiteMrkt Bun Run CapInv Invest1 Invest2 OHeads Elec Gas Office S IT Sup’s Phone Rent
203 $4112 $2400 $884 $15000 $5000 $340 $278 $140 $96 $220 $1450
204 120 121 122 123 130 131 132 200 210 220 230 240 250 260
205

Hi again Zax,
I hope this will make things a little clearer. This is a spreadsheet that I inherited when I took over the role of ‘book-keeper’ and have endeavoured to make it more user friendly and easier to extract data summaries without the time consuming manual manipulation. As I said, it may well be that the spreadsheet may need reformatting to make the coding easier.

SheetName = Apr-May 2013
Row 203 contains the sum of the columns 22 – 136
Row 204 contains the GL (general ledger) codes

At Cells(205, 22) : VBA will detect 120 and will SUMIF Row 204 starting at Cells(204, 23) all the codes < 129 (any code less than 129 will be sales). Sum_range will start at Cells(203, 23). Range( Cells(205, 22) will be named as a range:- TotSalesApr-May 2013

At Cells(205, 26) : VBA will detect 130 and will SUMIF Row 204 starting at Cells(204, 27) all the codes < 139 (any code less than 139 will be Capital Investment). Sum_range will start at Cells(203, 27). Range( Cells(205, 26) will be named as a range:- TotCapInvApr-May 2013

At Cells(205, 29) : VBA will detect 200 and will SUMIF Row 204 starting at Cells(204, 30) all the codes < 299 (any code less than 299 will be Overheads). Sum_range will start at Cells(203, 30). Range( Cells(205, 29) will be named as a range:- TotOHeadsApr-May 2013

Cheers

 

[tungchiu]

Sorry about the table - grid got cancelled on posting!