Insert a formula if there is a value on a cell in the same row

**abrilabs** · 04-03-2013, 09:41 PM

I need to get a macro that if a row has any value on column D, it creates a formula in column A, another formula in B, and another formula in C.

Column A's formula is =VALUE(RIGHT(D6,7))
Column B's formula is =CONCATENATE(D6,"_",TEXT(COUNTIF(D$4:$D6,D6),"000"))
Column C's formula is =CONCATENATE(D6,"_",G6)

Also, it needs to ignore rows 1,2,and 3 as the data starts on row 4.

Entering all these formulas as a template takes up a lot of space in the file since the data could be 300 rows or 30,000 depending on the day. This is why I need a macro so the formulas are only entered when there is a value that needs them.

Can you please help??

Thank you!

**:) Sixthsense :)** · 04-03-2013, 11:53 PM

Try the below code...

Change the Start and EndRw Values to your desired rows.

Sub AddFormulasAndConvertItAsValues()
Dim iStartRw As Integer, nEndrw As Long

iStartRw = 6
nEndrw = 35000

Range("A" & iStartRw & ":" & "A" & nEndrw).Formula = "=VALUE(RIGHT(D6,7)"
Range("B" & iStartRw & ":" & "B" & nEndrw).Formula = "=CONCATENATE(D6,""_"",TEXT(COUNTIF(D$4:$D6,D6),""000""))"
Range("C" & iStartRw & ":" & "C" & nEndrw).Formula = "=CONCATENATE(D6,""_"",G6)"

Range("A" & iStartRw & ":" & "C" & nEndrw).Value = Range("A" & iStartRw & ":" & "C" & nEndrw).Value

End Sub

Insert a formula if there is a value on a cell in the same row

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Insert a formula if there is a value on a cell in the same row

Re: Insert a formula if there is a value on a cell in the same row

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions