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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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.
 
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
 
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
 
Back
Top