Selective Cell input (97)

Topic

New Reply

Hi All,

I’m having trouble with this macro. I want to in a selected range (say a1 to d5000) to have a formula keyed into the cell only if the cell has an existing formula in it. The formulas to be keyed in each of the 4 columns abcd are different, but the formulas down each column is the same except for the row numbers.

Thanks.

Reply | Quote

Replies

Hi kingming. Try this code:
Sub SelectiveCell()
Dim MyRangeA, MyRangeB, MyRangeC, MyRangeD As Range
Set MyRangeA = Worksheets(“Sheet3”).Range(“A1:A18”)
For x = 1 To 18
If MyRangeA.Cells(x).Value = “” Then _
GoTo LastLine Else GoTo Line1
Line1:
MyRangeA.Cells(x).Formula = “=3*2”
LastLine:
Range(“A1”).Select
Next x
End Sub

You would need to insert your own ranges and formulae in the appropriate places in the code. You would also need to add similar code for your other ranges.

Regards
Rob

Reply | Quote

Thanks for your advice.

Reply | Quote

Your code will replace constants as well as formula.

Reply | Quote

HI,
Do you mean you want a formula added to the cell only if it already has a formula in it or only if it already has something in it? If it’s the former then try something like:

Dim rngCell as range
for each rngcell in selection
  with rngCell
    if left$(.formula,1) = "=" then
      select case .column
        case 1
          .formula = "formula for column A here"
        case 2
          .formula = "formula for column B here"
        case 3
          .formula = "formula for column C here"
        case 4
          .formula = "formula for column D here"
        case else
      end select
    end if
  end with
next rngCell

You can adjust this as necessary. Note: this is ‘air code’ so you may need to check it!
Hope that helps.

Reply | Quote

“Air code”? Is that what Elvis called it?

Reply | Quote

Hi Rory

I was interested in the solution you posted re: Selective Cell input. It is certainly more elegant than my effort. However, I’ve tried out the code but it doesn’t quite work. It only puts a formula (the correct one) into the active cell. It doesn’t loop through the for/next section of the code. I cannot figure out the cause. I have set rngCell to the appropriate range on the worksheet.
I’d be very greatful for your time and expertise.
I could see many useful applications of the code.

Regards

Rob

Reply | Quote

With Rory’s code you need to select all of the cells where you want the macro to operate (in this case A1:D5000) before running the macro. If you want to fix the cells in the macro, you could replace the For statement with:

For Each rngcell in Worksheets(“Sheet1”).Range(“A1:D5000”)

Reply | Quote

Thanks Legare

It works a treat.

Regards

Rob

Reply | Quote