shane_aldrich
Board Regular
- Joined
- Oct 23, 2009
- Messages
- 148
This is gonna be hard to verbalize, but I'll do my best.
Starting in Cell A4 I have a list of unique sales codes, there are duplicates, but it's already sorted.
There can be varying columns. I'm more or less trying to create subtotals w/o using the actual subtotal functionality.
The sub below finds the last used column, increments up by 1, and places it row 4. That formulas is more a less a T/F flag when the dealer code changes.
First, it's supposed to be referecning $A4, but it acutally reads RC1 in excel, not just VBA (not sure why)
Right now FH is the last used column (164)
so it should place the formula in FI4
FI4 should read =IF(NOT(UPPER(TRIM($A4))=UPPER(TRIM(OFFSET($A4,1,0)))),1,0)
Now the tricky part. I need to increment up one more column and place....
FJ4=IF(FI4=0,FJ3+B4,B4)
So...B4 is the second column, and after I do my T/F formula after the last used column, the next column should add the values starting in the second column...that formulas would carry to the right until there is a running total for every column used...
so since I am going to 164 now, the subtotals would carry to the 327
I think the thing I'm stuggeling with is not so much the VBA, but writing a flexible formula that can account for fluxuations in the number of columns.
I'm pretty sure how to get it to fill in correctly something like, I just can't wrap my mind around the formula
Dim lColFill As Long
lColFill = lLastCol+lLastCol
Range = Cells(4,lLastCol+2),(lLastRow,lColFill))
Sub Combine_SCPD()
'*************************
Dim lCol As Long
Dim lLastRow As Long
Dim lLastCol As Long
Dim wsSRC As Worksheet
Dim wsIMP As Worksheet
Dim wsDST As Worksheet
'*********************
Set wsIMP = Sheets("Import")
wsIMP.Activate
lLastRow = Last(1, Columns(1))
lLastCol = Last(2, Rows(1))
Cells(4, lLastCol + 1).Formula = "=IF(NOT(UPPER(TRIM(RC1))=UPPER(TRIM(OFFSET(RC1,1,0)))),1,0)"
End Sub
Starting in Cell A4 I have a list of unique sales codes, there are duplicates, but it's already sorted.
There can be varying columns. I'm more or less trying to create subtotals w/o using the actual subtotal functionality.
The sub below finds the last used column, increments up by 1, and places it row 4. That formulas is more a less a T/F flag when the dealer code changes.
First, it's supposed to be referecning $A4, but it acutally reads RC1 in excel, not just VBA (not sure why)
Right now FH is the last used column (164)
so it should place the formula in FI4
FI4 should read =IF(NOT(UPPER(TRIM($A4))=UPPER(TRIM(OFFSET($A4,1,0)))),1,0)
Now the tricky part. I need to increment up one more column and place....
FJ4=IF(FI4=0,FJ3+B4,B4)
So...B4 is the second column, and after I do my T/F formula after the last used column, the next column should add the values starting in the second column...that formulas would carry to the right until there is a running total for every column used...
so since I am going to 164 now, the subtotals would carry to the 327
I think the thing I'm stuggeling with is not so much the VBA, but writing a flexible formula that can account for fluxuations in the number of columns.
I'm pretty sure how to get it to fill in correctly something like, I just can't wrap my mind around the formula
Dim lColFill As Long
lColFill = lLastCol+lLastCol
Range = Cells(4,lLastCol+2),(lLastRow,lColFill))
Sub Combine_SCPD()
'*************************
Dim lCol As Long
Dim lLastRow As Long
Dim lLastCol As Long
Dim wsSRC As Worksheet
Dim wsIMP As Worksheet
Dim wsDST As Worksheet
'*********************
Set wsIMP = Sheets("Import")
wsIMP.Activate
lLastRow = Last(1, Columns(1))
lLastCol = Last(2, Rows(1))
Cells(4, lLastCol + 1).Formula = "=IF(NOT(UPPER(TRIM(RC1))=UPPER(TRIM(OFFSET(RC1,1,0)))),1,0)"
End Sub
