Entering array formula using VBA

[rob nobel]

The following is an array formula....

=IF(OR(G26="",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"",MAX(IF($G$16:G26=G26,$H
$16:H26)+1))

1. Is there a way to enter this into the cell (G26) using VBA (as an array)?
2. If so, if the row that this is to be entered is diff to row 26, how
does this get entered into say, G35 and the references changed
appropriately?


Rob

 

[Charles Williams]

Hi Rob,

try this

Sub rob()
Range("F26").FormulaArray =
"=IF(OR(G26="""",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G26=G
26,$H$16:H26)+1))"
Range("F26").Copy
Range("F35").PasteSpecial (xlPasteFormulas)
End Sub

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[rob nobel]

Thanks Charles. I'm sure that will do it but I may not have been very
clear. Is there a way to do this without copy/paste so that the formula is
entered into a variety of cells directly only via VBA code?

Rob

 

[Charles Williams]

Hi Rob,

not sure what you are looking for; there are many ways of doing this: here
are a couple more

Sub rob2()
Dim str1 As String
str1 =
"=IF(OR(RC[1]="""",VLOOKUP(RC[1],R16C7:R23C8,2,FALSE)=0),"""",MAX(IF(R16C7:R
C[1]=RC[1],R16C8:RC[2])+1))"
Range("F26").FormulaArray = str1
Range("F35").FormulaArray = str1
End Sub

Sub rob3()
Dim j As Long
j = 26
Range("F" & CStr(j)).FormulaArray = "=IF(OR(G" & CStr(j) & "="""",VLOOKUP(G"
& CStr(j) & ",$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G" & CStr(j) & "=G"
& CStr(j) & ",$H$16:H" & CStr(j) & ")+1))"
j = 35
Range("F" & CStr(j)).FormulaArray = "=IF(OR(G" & CStr(j) & "="""",VLOOKUP(G"
& CStr(j) & ",$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G" & CStr(j) & "=G"
& CStr(j) & ",$H$16:H" & CStr(j) & ")+1))"
End Sub


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[rob nobel]

Thanks Charles, I think I got lost in what I was trying to do but your
various pieces of code has helped a good deal.

Rob

Hi Rob,

not sure what you are looking for; there are many ways of doing this: here
are a couple more

Sub rob2()
Dim str1 As String
str1 =
"=IF(OR(RC[1]="""",VLOOKUP(RC[1],R16C7:R23C8,2,FALSE)=0),"""",MAX(IF(R16C7:R
C[1]=RC[1],R16C8:RC[2])+1))"
Range("F26").FormulaArray = str1
Range("F35").FormulaArray = str1
End Sub

Sub rob3()
Dim j As Long
j = 26
Range("F" & CStr(j)).FormulaArray = "=IF(OR(G" & CStr(j) & "="""",VLOOKUP(G"
& CStr(j) & ",$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G" & CStr(j) & "=G"
& CStr(j) & ",$H$16:H" & CStr(j) & ")+1))"
j = 35
Range("F" & CStr(j)).FormulaArray = "=IF(OR(G" & CStr(j) & "="""",VLOOKUP(G"
& CStr(j) & ",$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G" & CStr(j) & "=G"
& CStr(j) & ",$H$16:H" & CStr(j) & ")+1))"
End Sub


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

Thanks Charles. I'm sure that will do it but I may not have been very
clear. Is there a way to do this without copy/paste so that the formula is
entered into a variety of cells directly only via VBA code?

Rob

"=IF(OR(G26="""",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G26=G=IF(OR(G26="",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"",MAX(IF($G$16:G26=G26,$H