offset.cell

J

Jeff

Hello,

I need help designing a VBA Macro: I have in cell A1 "Paid" in cell G8
"total" in cell H8 $100.00

What I need is: to cell.find "paid" then to do cell.offset to G8 to
select.copy the value in H8. Is this possible ?
Regards,
 
D

Dave O

Minimally, the code looks like this:

Sub Copy_If_Paid()
If ActiveCell.Value = "Paid" Then
ActiveCell.Offset(0, 7).Copy
End If
End Sub

This works when the cell pointer is already on cell A1. A lot depends
on how your information is displayed on the spreadsheet. Do you have
many rows of Paid / Unpaid entries? Often I'll have a macro process
many rows in a spreadsheet, and I'll use a DO loop to repeat an
operation until it reaches the first blank row, or I enter the word
"Stop" when the data rows have ended.
What does your information look like?

Dave O
 
J

Jeff

Thank you.
But I still need to offset the activecell to "Total" to select.copy the
value in H. Since I won't know how many rows there are between "Paid" "Total"
Regards,
 
F

Frank Kabel

Hi
one way in VBA

dim return_value
return_value=application.vlookup("Total",range("G:H"),2,0)
msgbox return_value
 
J

Jeff

Thank you,
That's What I wanted, but I need first to do a cell.find in column A
for"paid" then I can do your macro.
Regards,
 
J

Jeff

Hi,
Because in my worksheet, I have in column A, the following texts:
Paid
Unpaid
Disputed.

That's why I need a cell.find first.
Thanks,
 
F

Frank Kabel

Hi
you may provide some more detail about how your complete spreadsheet looks
like and what in total you're trying to achieve
 
J

Jeff

Here's an example.
Cell.find "paid" then do activecell.offset to "total" to select "2,672.25"
A B
G H
Paid Gate Safe Catering sealing 52.25 Turn 21 DL Global Selectee
PIF 40.00 Turn 0
Aircraft Search 75.00 Turn
City of Philadelphia 29.00 Print
TOTAL $2,672.25
Thanks,
 
J

Jeff

Jeff said:
Here's an example.
Cell.find "paid" then do activecell.offset to "total" to select "2,672.25"
A B G H
Paid Gate Safe Catering sealing 52.25 Turn 21 DL Global Selectee PIF 40.00 Turn 0 Aircraft Search 75.00 Turn
Click to expand...
City of Philadelphia 29.00 Print TOTAL
$2,672.25
 
F

Frank Kabel

Hi
but you stated that column A can also contain other values. Really not sure
what you have to check. Maybe (but just a wild guess):

sub foo()
dim startpos as long
dim ret_value
startpos=application.Match("Paid",Range("A:A"),0)
if not iserror(startpos) then
ret_value=application.vlookup("Total",Range("G" & startpos &
"H1000"),2,0)
msgbox ret_value
end if
end sub
 
J

Jeff

Hi,
Thank you again that is I wanted.
But I'm getting a Run-tiem error 1004 on this line:
ret_value=application.vlookup("Total",Range("G" & startpos & "H1000"),2,0)
Thanks,
 
F

Frank Kabel

Hi
typo. Use:
ret_value=application.vlookup("Total",Range("G" & startpos & ":H1000"),2,0)
 
Top