| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
offset.cell
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, |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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, "Dave O" wrote: 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 |
|
#4
|
|||
|
|||
|
Hi
is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#5
|
|||
|
|||
|
Total is always in column G and the value I need always in column H
Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#6
|
|||
|
|||
|
Hi
one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#7
|
|||
|
|||
|
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, "Frank Kabel" wrote: Hi one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#8
|
|||
|
|||
|
Hi
why do you need this 'find' command first? -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#9
|
|||
|
|||
|
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, "Frank Kabel" wrote: Hi why do you need this 'find' command first? -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#10
|
|||
|
|||
|
Hi
you may provide some more detail about how your complete spreadsheet looks like and what in total you're trying to achieve -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi why do you need this 'find' command first? -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#11
|
|||
|
|||
|
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, "Frank Kabel" wrote: Hi you may provide some more detail about how your complete spreadsheet looks like and what in total you're trying to achieve -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi why do you need this 'find' command first? -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#12
|
|||
|
|||
|
"Jeff" wrote: 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, "Frank Kabel" wrote: Hi you may provide some more detail about how your complete spreadsheet looks like and what in total you're trying to achieve -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi why do you need this 'find' command first? -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#13
|
|||
|
|||
|
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 -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi you may provide some more detail about how your complete spreadsheet looks like and what in total you're trying to achieve -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi why do you need this 'find' command first? -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#14
|
|||
|
|||
|
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, "Frank Kabel" wrote: 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 -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi you may provide some more detail about how your complete spreadsheet looks like and what in total you're trying to achieve -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi why do you need this 'find' command first? -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, "Frank Kabel" wrote: Hi one way in VBA dim return_value return_value=application.vlookup("Total",range("G: H"),2,0) msgbox return_value -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Total is always in column G and the value I need always in column H Thanks, "Frank Kabel" wrote: Hi is your 'Total' label always in column G? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: 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, |
|
#15
|
|||
|
|||
|
Hi
typo. Use: ret_value=application.vlookup("Total",Range("G" & startpos & ":H1000"),2,0) -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, |
|
#16
|
|||
|
|||
|
Thank you again,
Regards, Jeff "Frank Kabel" wrote: Hi typo. Use: ret_value=application.vlookup("Total",Range("G" & startpos & ":H1000"),2,0) -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... 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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
