CODEWITHSUNDEEP
excelvba
JohnHC
JohnHC

Reputation: 11195

Enter a formula into cells with VBA

Ok, so this is likely the combination of a number of existing questions, but here goes...

I have a requirement to copy data from one sheet to another, then change some data, then add a column to the end with the following:

=if(G2="+",1,0)

This then needs to run down the entirety of the dataset, but the number of rows varies.

Is there a way I can get this formula to appear next to every row that contains data and no further using vba?

Upvotes: 0

Views: 113

Answers (2)

CaptainABC
CaptainABC

Reputation: 1239

Yes you can!

Sub ABC()
Dim ws As Worksheet
Dim Lastrow As Long

Set ws = ThisWorkbook.Worksheets("Sheet1") ' change to relevant sheet

With ws
Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row ' change "A" to correct column

.Range("A1:A" & Lastrow).Formula = "=if(G2="+",1,0)" 'change "A1:A" to correct column

' convert formulas to values if you like:
.Range("A1:A" & Lastrow).Value = .Range("A1:A" & Lastrow).Value 'again change "A1:A" to correct column
End With

End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Here is an example in which the formula column is column H

Sub qwertyu()
    Dim N As Long
    N = Cells(Rows.Count, "G").End(xlUp).Row
    Dim xCol As String
    xCol = "H"
    Range(xCol & 2 & ":" & xCol & N).Formula = "=if(G2=""+"",1,0)"
End Sub

Change the column ID to suit your needs.

Upvotes: 1

Related Questions