Cell Value Automation

[truitt20]

Currently I have the following code and was wondering how to condense it b/c i do not want to keep writing this for every single row I add to the sheet. The sheet will vary in length.

Sub CQG_Update()

Application.ScreenUpdating = False

'Row 3'
Range("D3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("D1").Value
Range("E3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("E1").Value
Range("F3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("F1").Value
Range("G3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("G1").Value
Range("H3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("H1").Value
Range("I3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("I1").Value
Range("J3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("J1").Value
Range("K3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("K1").Value
Range("L3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("L1").Value
Range("M3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("M1").Value
Range("O3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("O1").Value
Range("P3").Formula = "=CQGPC|" & Range("A3").Value & "!" & Range("P1").Value

'Row4'
Range("D4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("D1").Value
Range("E4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("E1").Value
Range("F4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("F1").Value
Range("G4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("G1").Value
Range("H4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("H1").Value
Range("I4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("I1").Value
Range("J4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("J1").Value
Range("K4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("K1").Value
Range("L4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("L1").Value
Range("M4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("M1").Value
Range("O4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("O1").Value
Range("P4").Formula = "=CQGPC|" & Range("A4").Value & "!" & Range("P1").Value

Application.ScreenUpdating = True


End Sub


Any help is much appreciated.

thanks

jt

 

[SkipVought]

Hi,

For lRow = 3 to 4
for iCol = cells(1,"D").column to cells(1,"P).column
cells(lrow, icol).value = cells(1, icol).value
next
next

Skip,

_[red][/red]

 

[PHV]

A starting point:
Range("D3").Formula = "=""CQGPC|"" & $A3 & ""!"" & D$1"
With Range("D34")
.FillRight
.FillDown
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[truitt20]

PHV,

Thanks for the help, but when I run:

Range("D3").Formula = "=""CQGPC|"" & $A3 & ""!"" & D$1"
With Range("D34")
.FillRight
.FillDown
End With

It populates the cells with the following CQGPC|P.CCH71500!LongDescription. I need to add the "=" sign to the beginning. Anyway to manipulate the code for this to work. I have been playing around with it but to no avail.

thanks

jt

 

[PHV]

Works for me (XL2003), ie I have the = sign and the cells references in the formula bar.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886