MACRO: Add formula's across row if criteria is met

[SteveT]

Hello !,

In effort to reduce size of lookup, dependent workbook, I would like to have
formulas placed across columns A:F in dependent worksheet if 2 cell values
criterias are met.

A1 = find first row in 'Source' worksheet where criteria value is met column A
B1 = find last row in 'Source' worksheet where value is present column F

column A 'Source' worksheet = date value
column F 'Source' worksheet = final update for row.

Trying to keep question simple, hopefully not to simple.

thanks for any help,
Steven / Dallas

 

[Dave Peterson]

=match(a1,Source!a:a,0)
will give you the row number where A1 first matches something in column A of
Source.

=LOOKUP(2,1/(Source!F1:F999=B1),ROW(Source!F1:F999))
(Make that 999 big enough to extend past the last possible row--but don't use
the whole column.)

will return the row of the last matching value (value in B1) in source F1:f999.

 

[Dave Peterson]

If you really need a macro, you can use those formulas in the code, too.

Option Explicit
Sub testme()

Dim res1 As Variant
Dim res2 As Variant

res1 = Application.Match("somevalue", Worksheets("Source").Range("a:a"), 0)
If IsError(res1) Then
MsgBox "Not found"
Else
MsgBox "found on row: " & res1
End If

res2 = Application.Evaluate _
("lookup(2,1/(Source!F1:F999=""x""),ROW(Source!F1:F999))")

If IsError(res2) Then
MsgBox "Not found"
Else
MsgBox "found on row: " & res2
End If


End Sub

 

[SteveT]

Thanks for the response Dave,

I'm actualling looking for the macro which will add the formula down the
rows if criteria met.


Brgds, Steven