Editing a cell

  • Thread starter Thread starter kencote1
  • Start date Start date
K

kencote1

Watch for linewrap.

I need to edit a cell with a number, add a = before it, a + after it
and then the number from the cell to the right of it.

There was a similar post about taking a cell content and adding a
number that was asked for:

Sub CreateFormula()
Dim dblNum As Double, dblAdd As Double


dblNum = ActiveCell.Value
dblAdd = Application.InputBox("Please enter the number to add.",
Type:=2)
ActiveCell.Formula = "= " & dblNum & "+" & dblAdd & ""


End Sub

How do I adjust it to put in another adjacent cell instead of the
requested number?
 
Sub CreateFormula()
With ActiveCell
.Formula = "=" & .Value & "+" & .Offset(0,1).Value
End with
End Sub
 
That worked. Now I need it to step through a range of rows, and if A >1
then perform the function until A is not populated
 
I tried


For RowNdx = 50 To 1 Step -1
If Cells(RowNdx, "a").Value > 1 Then
With ActiveCell
.Formula = "=" & .Value & "+" & .Offset(0, 1).Value
End With
End If
Next
End Sub

But for some reason it stays in the row it's in and performs the
operation 50 times. Why won't it go to another row?
 
For RowNdx = 50 To 1 Step -1
With Cells(RowNdx,"a")
If .Value > 1 Then
.Formula = "=" & .Value & "+" & .Offset(0, 1).Value
End If
End with
Next
End Sub

If you are checking column A, but wish to put the formula in say column E

For RowNdx = 50 To 1 Step -1
With Cells(RowNdx,"a")
If .Value > 1 Then
with .Offset(0,4)
.Formula = "=" & .Value & "+" & .Offset(0, 1).Value
end With
End If
End with
Next
End Sub

The reason your code doesn't work is you never change the activecell.
 
Excellent!
You have saved the day.


Tom said:
For RowNdx = 50 To 1 Step -1
With Cells(RowNdx,"a")
If .Value > 1 Then
.Formula = "=" & .Value & "+" & .Offset(0, 1).Value
End If
End with
Next
End Sub

If you are checking column A, but wish to put the formula in say column E

For RowNdx = 50 To 1 Step -1
With Cells(RowNdx,"a")
If .Value > 1 Then
with .Offset(0,4)
.Formula = "=" & .Value & "+" & .Offset(0, 1).Value
end With
End If
End with
Next
End Sub

The reason your code doesn't work is you never change the activecell.
Click to expand...
 
Back
Top