Solved: Enter Formula on Multiple Locations using Macros

[klutz]

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 ,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...

[klutz]

OK,

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

[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,

[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

[klutz]

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?

[klutz]

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.