Find coding

[Alberta Rose]

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

 

[JLGWhiz]

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

 

[LOFE]

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

 

[Alberta Rose]

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?

 

[Alberta Rose]

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

 

[Alberta Rose]

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?