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

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

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

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

SteveT wrote:

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

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

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 wrote:

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

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

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

"Dave Peterson" wrote:

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

SteveT wrote:

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