Solved: Enter Formula on Multiple Locations using Macros

Hi Guys,

I have the following

[VBA]Sub LargestGPW()
'
Range("E27").Select
Selection.FormulaArray = _
"=LOOKUP(2,1/((R27C7:R200C7=RC[2])*(R27C29:R200C29=RC[1])),R27C11:R200C11)"
Range("F28").Select
Selection.FormulaArray = "=MAX(IF(R27C7:R200C7=RC[1],R27C29:R200C29))"
Range("E27").Select

End Sub[/VBA]

which gives me a location name along with that locations maximum value.

The formula looks for a condition met from column G, matches it to column AC and then gives me the result from column K , here is the formula
{=LOOKUP(2,1/(($G$27:$G$200=G27)*($AC$27:$AC$200=F27)),$K$27:$K$200)}.

What i need is to be able to insert that same formula by first looking down column G next available empty row and once it finds it insert the formula on column E and F one row below where the empty row is first found from column G.

Sounds:bug: ,I can manually enter formula then copy paste in the proper row and column but the problem I have others will be using this worksheet and I wanted to this automated.

Thanks...

OK,

I have this now but it is not working as envisioned,

Code:

---

[VBA]Sub LargestGPW()

Range("g65536").End(xlUp).Select
With Selection
.Offset(2, -2).FormulaArray = _
"=LOOKUP(2,1/((R27C7:R200C7=RC[2])*(R27C29:R200C29=RC[1])),R27C11:R200C11)"
.Offset(3, -1).FormulaArray = "=MAX(IF(R27C7:R200C7=RC[1],R27C29:R200C29))"
End With


End Sub[/VBA]

---

it is only looking at the last empty row from the bottom up on column G. I need it to look down from column G find an empty cell an then place the formulas.

Any thoughts?

Thanx,:dunno

Hi there,

You may be able to do something like this...

[vba]Option Explicit

Sub LargestGPW()
Dim ws As Worksheet, LastRow As Long, EndRow As Long
Dim StartRow As Long, FirstRow As Long, sFormula As String
Set ws = ActiveSheet '?
LastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
If Len(ws.Cells(LastRow, "G").Offset(-1, 0).Value) = 0 Then
StartRow = LastRow - 1
Else
StartRow = ws.Cells(LastRow, "G").End(xlUp).Row
End If
FirstRow = 27 'change if needed!
EndRow = 200 'change if needed!
If Len(ws.Cells(StartRow, "E").Value) > 0 Then
MsgBox "You already have values for the last data set!", vbExclamation
Exit Sub
End If
sFormula = "=LOOKUP(2,1/((G" & FirstRow & ":G" & LastRow & "=G" & StartRow & ")*(AC"
sFormula = sFormula & FirstRow & ":AC" & LastRow & "=F" & StartRow + 1 & ")),K"
sFormula = sFormula & FirstRow & ":K" & LastRow & ")"
ws.Range("E" & FirstRow & ":F" & FirstRow).MergeCells = True
ws.Cells(StartRow, "E").FormulaArray = sFormula
sFormula = "=MAX(IF(G" & FirstRow & ":G" & LastRow & "=G" & StartRow & ",AC"
sFormula = sFormula & FirstRow & ":AC" & LastRow & "))"
ws.Cells(StartRow + 1, "F").FormulaArray = sFormula
End Sub[/vba]

What happens when your data set grows? You could choose to update those formulas. You've hardcoded the start and last rows in the range I see. There may be better ways to make your code a bit more robust.

HTH

Quote:

---

Originally Posted by Zack Barresse

Hi there,

You may be able to do something like this...

[vba]Option Explicit

Sub LargestGPW()
Dim ws As Worksheet, LastRow As Long, EndRow As Long
Dim StartRow As Long, FirstRow As Long, sFormula As String
Set ws = ActiveSheet '?
LastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
If Len(ws.Cells(LastRow, "G").Offset(-1, 0).Value) = 0 Then
StartRow = LastRow - 1
Else
StartRow = ws.Cells(LastRow, "G").End(xlUp).Row
End If
FirstRow = 27 'change if needed!
EndRow = 200 'change if needed!
If Len(ws.Cells(StartRow, "E").Value) > 0 Then
MsgBox "You already have values for the last data set!", vbExclamation
Exit Sub
End If
sFormula = "=LOOKUP(2,1/((G" & FirstRow & ":G" & LastRow & "=G" & StartRow & ")*(AC"
sFormula = sFormula & FirstRow & ":AC" & LastRow & "=F" & StartRow + 1 & ")),K"
sFormula = sFormula & FirstRow & ":K" & LastRow & ")"
ws.Range("E" & FirstRow & ":F" & FirstRow).MergeCells = True
ws.Cells(StartRow, "E").FormulaArray = sFormula
sFormula = "=MAX(IF(G" & FirstRow & ":G" & LastRow & "=G" & StartRow & ",AC"
sFormula = sFormula & FirstRow & ":AC" & LastRow & "))"
ws.Cells(StartRow + 1, "F").FormulaArray = sFormula
End Sub[/vba]

What happens when your data set grows? You could choose to update those formulas. You've hardcoded the start and last rows in the range I see. There may be better ways to make your code a bit more robust.

HTH

---

(I don't foresee the data growing past row 200, but if it does than i'll change the end row as suggested...)

Thanx for your help but it isn't doing what i envisioned. It only places data on the first empty cell next to column G and when I run the macro again I get the message box message of --You already have values for the last data set!--

Even when i clear the cells and run the macro i again get the ---You already have values for the last data set!---

Any thoughts?

Solved..

[VBA]Sub LargestGPW()
'
Dim lngLastRow As Long, rngData As Range, rngCell As Range
lngLastRow = Cells(Rows.Count, "g").End(xlUp).Row
On Error Resume Next
Set rngData = Range("G26:G" & lngLastRow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rngData Is Nothing Then
For Each rngCell In rngData
With rngCell
.Offset(1, -2).FormulaR1C1 = "=LOOKUP(2,1/((R27C7:R" & lngLastRow & "C7=RC[2])*(R27C29:R" & _
lngLastRow & "C29=R[1]C[1])),R27C11:R" & lngLastRow & "C11)"
.Offset(2, -1).FormulaArray = "=MAX(IF(R27C7:R" & lngLastRow & "C7=RC[1],R27C29:R" & lngLastRow & "C29))"
End With
Next rngCell
End If
End Sub[/VBA]

Here is the code 4 da benefit of others.