Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Cell click, start macro
Here is what I am looking to do. I need to make this macro where, when I
click on a cell in column B on sheet one, it would copy the contents of that cell, and insert the value into sheet two in Range("B4"), then call the macro Searchforstring. How can I do this? Here is what I have so far... Sub SearchandFind() On Cell Click ActiveCell.Copy Sheets("Sheet2").Activate Range("B4").PasteSpecial xlPasteValues Call SearchForString End Sub Obviously the "On Cell Click" is incorrect. Does anyone have any possible idea as to how I can complete this? Thank you for any help that you can be! In case anyone is wondering or want to see the SearchForString vba (for incorporation into this macro, or to tell me how inefficient this macro is, or suggestions on another way to accomplish this): Sub SearchForString() Profit = Range("B4") Sheets("Sheet2").Select Range("A2:K100").ClearContents Sheets("$1k Detail").Select Dim LSearchRow As Integer Dim LCopyToRow As Integer On Error GoTo Err_Execute 'Start search in row 2 LSearchRow = 2 'Start copying data to row 2 in Sheet2 (row counter variable) LCopyToRow = 2 While Len(Range("B" & CStr(LSearchRow)).Value) 0 'If value in column B = Range "Profit", copy entire row to Sheet1 If Range("B" & CStr(LSearchRow)).Value = Profit Then 'Select row in Sheet1 to copy Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Copy 'Paste row into Sheet2 in next row Sheets("Sheet2").Select Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select ActiveSheet.Paste 'Move counter to next row LCopyToRow = LCopyToRow + 1 'Go back to Sheet1 to continue searching Sheets("$1k Detail").Select End If LSearchRow = LSearchRow + 1 Wend 'Position on cell A3 Application.CutCopyMode = False Sheets("Sheet2").Select Range("A2").Select Exit Sub Err_Execute: MsgBox "An error occurred." End Sub THANK YOU! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Cell click, start macro
Don,
Thank you for your time. This is exactly what I wanted to do, so you have been a great help. Robert Don Guillett wrote: How about a double click event? Right click sheet tabview codeinsert this. Now when you DOUBLE click on cell b4 cell b4 in sheet 2 will be populated. If you really want it to be automatic use a worksheet_change event instead which will just do it automatically when you change the value in cell b4 or the source sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address < "$B$4" Then Exit Sub Sheets("sheet2").Range("b4") = Target 'call mymacroname End Sub For the rest of your code may I suggest you look in the vba help index for FINDNEXT instead. Do NOT use selections where not necessary. Here is what I am looking to do. I need to make this macro where, when I click on a cell in column B on sheet one, it would copy the contents of [quoted text clipped - 83 lines] THANK YOU! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Cell click, start macro
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Cell click, start macro
I am going to have to teach myself FINDNEXT, but as soon as I get that down
and change the macro I will change it here. I am always wanting to build my vba base, so thank you for your input, as it gives me a new direction to learn in vba. Don Guillett wrote: Glad to help. Post your improved 2nd macro using FINDNEXT Don, [quoted text clipped - 25 lines] THANK YOU! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200801/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Start a macro when click on tab sheet | Excel Programming | |||
Macro to start when cell selected | Excel Discussion (Misc queries) | |||
Can I start a macro from a cell by using a formula | Excel Worksheet Functions | |||
Can't start macro via button click | Excel Programming | |||
Start macro on cell change | Excel Programming |