Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find coding
I have a spreadsheet that has different row numbers each month. I have a
column in this spreadsheet called Default that needs specific formulas put in depending on another column's data (called CC/CT). Since the position of these rows varies each month, I need to write a macro that goes through the CC/CT column and assigns the proper formula in the Default column, depending on the CC/CT. I think I need a Loop macro as well as a Find Macro but have not had much experience in either in Excel. Can anyone give me guidance? Thanks....Laurie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find coding
You could probably use a For...Each...Next loop to run colum CC?CT and if a
match is found, use use the found cell row reference to populate the cell in column "Default" exmpl: Dim rng As Range Set rng = ActiveSheet.Range(Cells(2, "CC/CT"), Cells(100, "CC/CT")) For Each c In rng If c = 'Some value Then ActiveSheet.Cells(c.Row, "Default") = 'Some formula End If Next "Alberta Rose" wrote: I have a spreadsheet that has different row numbers each month. I have a column in this spreadsheet called Default that needs specific formulas put in depending on another column's data (called CC/CT). Since the position of these rows varies each month, I need to write a macro that goes through the CC/CT column and assigns the proper formula in the Default column, depending on the CC/CT. I think I need a Loop macro as well as a Find Macro but have not had much experience in either in Excel. Can anyone give me guidance? Thanks....Laurie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find coding
You could also just use a Do...Until...Loop going through each row:
Range("A1").Select Do Until Activecell = "' 'Assuming there is data in each row If Activecell = "CT" Then Activecell(1, 6) = MyFormula 'Where 1 = the Row number and 6 = the column number from the activecell Else Activecell(1,6) = MyOtherFormula End If Activecell(2,1).Select 'To go down to the next row Loop "JLGWhiz" wrote: You could probably use a For...Each...Next loop to run colum CC?CT and if a match is found, use use the found cell row reference to populate the cell in column "Default" exmpl: Dim rng As Range Set rng = ActiveSheet.Range(Cells(2, "CC/CT"), Cells(100, "CC/CT")) For Each c In rng If c = 'Some value Then ActiveSheet.Cells(c.Row, "Default") = 'Some formula End If Next "Alberta Rose" wrote: I have a spreadsheet that has different row numbers each month. I have a column in this spreadsheet called Default that needs specific formulas put in depending on another column's data (called CC/CT). Since the position of these rows varies each month, I need to write a macro that goes through the CC/CT column and assigns the proper formula in the Default column, depending on the CC/CT. I think I need a Loop macro as well as a Find Macro but have not had much experience in either in Excel. Can anyone give me guidance? Thanks....Laurie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find coding
Thanks for the info. Another twist. Do I put the four available formulas to
use in a box to the right of the data and reference that appropriate cell? The formula may be used in 30 rows throughout the spreadsheet, how do I write the formula to enable it to determine which row # to include in the formula? Is there a wild card feature I put in the formula table? For example: the CC/CT column is column E. The default column where I want to populate the formulas is column T. Depending on what excel finds in column E, I want it to go to the available formulas and pick the one I specify, put the appropriate row number in the formula and populate the Default column row. The go to the next record and perform the same check. Can this be done? "LOFE" wrote: You could also just use a Do...Until...Loop going through each row: Range("A1").Select Do Until Activecell = "' 'Assuming there is data in each row If Activecell = "CT" Then Activecell(1, 6) = MyFormula 'Where 1 = the Row number and 6 = the column number from the activecell Else Activecell(1,6) = MyOtherFormula End If Activecell(2,1).Select 'To go down to the next row Loop "JLGWhiz" wrote: You could probably use a For...Each...Next loop to run colum CC?CT and if a match is found, use use the found cell row reference to populate the cell in column "Default" exmpl: Dim rng As Range Set rng = ActiveSheet.Range(Cells(2, "CC/CT"), Cells(100, "CC/CT")) For Each c In rng If c = 'Some value Then ActiveSheet.Cells(c.Row, "Default") = 'Some formula End If Next "Alberta Rose" wrote: I have a spreadsheet that has different row numbers each month. I have a column in this spreadsheet called Default that needs specific formulas put in depending on another column's data (called CC/CT). Since the position of these rows varies each month, I need to write a macro that goes through the CC/CT column and assigns the proper formula in the Default column, depending on the CC/CT. I think I need a Loop macro as well as a Find Macro but have not had much experience in either in Excel. Can anyone give me guidance? Thanks....Laurie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find coding
Yes, I think this is what I'm looking for. In your coding below, all that
would need to be changed is the 'some value and 'some formula? I'm sorry if this sounds like an elementary questions, but I am :) Thanks, Laurie "JLGWhiz" wrote: You could probably use a For...Each...Next loop to run colum CC?CT and if a match is found, use use the found cell row reference to populate the cell in column "Default" exmpl: Dim rng As Range Set rng = ActiveSheet.Range(Cells(2, "CC/CT"), Cells(100, "CC/CT")) For Each c In rng If c = 'Some value Then ActiveSheet.Cells(c.Row, "Default") = 'Some formula End If Next "Alberta Rose" wrote: I have a spreadsheet that has different row numbers each month. I have a column in this spreadsheet called Default that needs specific formulas put in depending on another column's data (called CC/CT). Since the position of these rows varies each month, I need to write a macro that goes through the CC/CT column and assigns the proper formula in the Default column, depending on the CC/CT. I think I need a Loop macro as well as a Find Macro but have not had much experience in either in Excel. Can anyone give me guidance? Thanks....Laurie |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find coding
Thanks for the info. Another twist. Do I put the four available formulas to
use in a box to the right of the data and reference that appropriate cell? The formula may be used in 30 rows throughout the spreadsheet, how do I write the formula to enable it to determine which row # to include in the formula? Is there a wild card feature I put in the formula table? For example: the CC/CT column is column E. The default column where I want to populate the formulas is column T. Depending on what excel finds in column E, I want it to go to the available formulas and pick the one I specify, put the appropriate row number in the formula and populate the Default column row. The go to the next record and perform the same check. Can this be done? "JLGWhiz" wrote: You could probably use a For...Each...Next loop to run colum CC?CT and if a match is found, use use the found cell row reference to populate the cell in column "Default" exmpl: Dim rng As Range Set rng = ActiveSheet.Range(Cells(2, "CC/CT"), Cells(100, "CC/CT")) For Each c In rng If c = 'Some value Then ActiveSheet.Cells(c.Row, "Default") = 'Some formula End If Next "Alberta Rose" wrote: I have a spreadsheet that has different row numbers each month. I have a column in this spreadsheet called Default that needs specific formulas put in depending on another column's data (called CC/CT). Since the position of these rows varies each month, I need to write a macro that goes through the CC/CT column and assigns the proper formula in the Default column, depending on the CC/CT. I think I need a Loop macro as well as a Find Macro but have not had much experience in either in Excel. Can anyone give me guidance? Thanks....Laurie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the color coding with Find & Replace? | Excel Discussion (Misc queries) | |||
Cannot find inefficient coding loop | Excel Programming | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming | |||
VB coding for showing "find" dialogue box? | Excel Programming |