CODEWITHSUNDEEP
excelvba
surendra choudhary
surendra choudhary

Reputation: 65

To run macro in each cell

I'm trying to run the goal-seek function in each non-empty cell of a column. but my following code isn't working to do this task. please let me know what I'm doing wrong here. my code looks like this

Sub Test2()

Dim C As Range
Dim i As Long

With ThisWorkbook.Sheets("final")
    For Each C In .Range("AH11", .Cells(.Rows.Count, "AH").End(xlUp))
        If .Cells(C, "AH") = vbNullString Then GoTo SkipRow
           ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Offset(0, -5).Range( _
    "A1")
SkipRow:
    Next C
End With

End Sub

It showing error "If .Cells(C, "AH") = vbNullString"

my task look like this enter image description here

Image for comment enter image description here

Upvotes: 0

Views: 290

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

After changing ActiveCell with c, as I recommended in my comment, it looks that running the code triggers a UDF function. So, you must use:

Application.Calculation = xlCalculationManual

before the code line:

For Each C In .Range("AH11", .Cells(.Rows.Count, "AH").End(xlUp))

and

Application.Calculation = xlCalculationAutomatic

after

 Next C

Upvotes: 1

Darrell H
Darrell H

Reputation: 1886

This is not a correct designation:

ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Offset(0, -5).Range("A1")

The changing cell means nothing. If you want to change the cell 5 columns to the left of the cell in column AH. You would use:

C.GoalSeek Goal:=0, ChangingCell:=C.Offset(0,-5)

Adding Range("A1") makes this invalid.

Upvotes: 0

Related Questions