PDA

View Full Version : Solved: Coding a button to activate action



kdigital
12-04-2008, 05:27 AM
I was fortunate to get code that would allow a sheet to do a series of instructions when an event happened on a cell. I was wondering if I could get the instruction to execute only when the user pressed a command button. This would give the user the freedom to enter all the accounts into the account column and then excute the code at one time. I tried to convert the code that was given to me but for some reason I'm getting an error. I created the sub then I just call it when the command button is pressed.

Sub fillincell()
Dim nRow As Long
Dim selection As Range


Set selection = Reportsheet.Range("h3.h200")

nRow = selection("A1").End(xlDown).Row + 1
selection(nRow, "A").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,2,FALSE)"
selection(nRow, "B").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,6,FALSE)"
selection(nRow, "C").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,3,FALSE)"
selection(nRow, "D").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,2,FALSE)"
selection(nRow, "E").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,3,FALSE)"
selection(nRow, "F").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,4,FALSE)"
selection(nRow, "G").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,4,FALSE)"
selection(nRow, "H").Value = Reportsheet.cell("h3").Value
selection(nRow, "I").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,5,FALSE)"
selection(nRow, "J").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,6,FALSE)"
selection(nRow, "K").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,7,FALSE)"
selection(nRow, "L").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,8,FALSE)"
selection(nRow, "M").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,9,FALSE)"
selection(nRow, "R").Formula = "=VLOOKUP(H" & nRow & ",trliqlookup!A2:i600,2,FALSE)"
selection(nRow, "S").Formula = "=VLOOKUP(H" & nRow & ",trliqlookup!A2:i600,3,FALSE)"


End Sub

mdmackillop
12-04-2008, 06:42 AM
Try

Sub fillincell()
Dim nRow As Long
Dim Rng As Range
Set Rng = Sheets("Reportsheet").Range("h3:h200")
nRow = Rng.Range("A1").End(xlDown).Row + 1
Rng(nRow, "A").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,2,FALSE)"
Rng(nRow, "B").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,6,FALSE)"
Rng(nRow, "C").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,3,FALSE)"
Rng(nRow, "D").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,2,FALSE)"
Rng(nRow, "E").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,3,FALSE)"
Rng(nRow, "F").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,4,FALSE)"
Rng(nRow, "G").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,4,FALSE)"
Rng(nRow, "H").Value = Sheets("Reportsheet").Range("h3").Value
Rng(nRow, "I").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,5,FALSE)"
Rng(nRow, "J").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,6,FALSE)"
Rng(nRow, "K").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,7,FALSE)"
Rng(nRow, "L").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,8,FALSE)"
Rng(nRow, "M").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,9,FALSE)"
Rng(nRow, "R").Formula = "=VLOOKUP(H" & nRow & ",trliqlookup!A2:i600,2,FALSE)"
Rng(nRow, "S").Formula = "=VLOOKUP(H" & nRow & ",trliqlookup!A2:i600,3,FALSE)"

End Sub

kdigital
12-04-2008, 07:40 AM
I'm getting run time error 1004 I noticed when the code was written for activing when a cell was changed. The forumla was written using Me.Range I'm going to try that modification and see if it works.

mdmackillop
12-04-2008, 10:13 AM
Can you post a sample workbook?

kdigital
01-02-2009, 11:06 AM
ok sorry took me song long to get back...the holidays and all that.. I was finally able to get the code to run correctly. In an effort to contribue to the group I will post the working code cause it may help someone else. Also I am aware that code can always be tweaked and made better. Thanks for your help :)

Sub fillincell()
Dim nRow As Long
Dim cnt As Integer
Dim Rng As Range
Set Rng = Range("a1:s400")
cnt = WorksheetFunction.CountA(Range("Q3:Q400"))
For i = 1 To cnt
nRow = Range("A1").End(xlDown).Row + 1
Rng(nRow, "A").Formula = "=VLOOKUP(Q" & nRow & ",vollookup!A2:f600,2,FALSE)"
Rng(nRow, "B").Formula = "=VLOOKUP(Q" & nRow & ",vollookup!A2:f600,6,FALSE)"
Rng(nRow, "C").Formula = "=VLOOKUP(Q" & nRow & ",vollookup!A2:f600,3,FALSE)"
Rng(nRow, "D").Formula = "=VLOOKUP(Q" & nRow & ",Jan!A2:m800,2,FALSE)"
Rng(nRow, "E").Formula = "=VLOOKUP(Q" & nRow & ",Feb!A2:m800,2,FALSE)"
Rng(nRow, "F").Formula = "=VLOOKUP(Q" & nRow & ",Mar!A2:m800,2,FALSE)"
Rng(nRow, "G").Formula = "=VLOOKUP(Q" & nRow & ",Apr!A2:m800,2,FALSE)"
Rng(nRow, "H").Formula = "=VLOOKUP(Q" & nRow & ",May!A2:m800,2,FALSE)"
Rng(nRow, "I").Formula = "=VLOOKUP(Q" & nRow & ",Jun!A2:m800,2,FALSE)"
Rng(nRow, "J").Formula = "=VLOOKUP(Q" & nRow & ",Jul!A2:m800,2,FALSE)"
Rng(nRow, "K").Formula = "=VLOOKUP(Q" & nRow & ",Aug!A2:m800,2,FALSE)"
Rng(nRow, "L").Formula = "=VLOOKUP(Q" & nRow & ",Sep!A2:m800,2,FALSE)"
Rng(nRow, "M").Formula = "=VLOOKUP(Q" & nRow & ",Oct!A2:m800,2,FALSE)"
Rng(nRow, "N").Formula = "=VLOOKUP(Q" & nRow & ",vollookup!A2:m800,5,FALSE)"

Rng(nRow, "P").Formula = "=VLOOKUP(Q" & nRow & ",vollookup!A2:f600,4,FALSE)"
Rng(nRow, "R").Formula = "=VLOOKUP(Q" & nRow & ",netpllookup!A2:m2500,2,FALSE)"
Rng(nRow, "S").Formula = "=VLOOKUP(Q" & nRow & ",netpllookup!A2:m2500,3,FALSE)"
Rng(nRow, "T").Formula = "=VLOOKUP(Q" & nRow & ",netpllookup!A2:m2500,4,FALSE)"
Rng(nRow, "U").Formula = "=VLOOKUP(Q" & nRow & ",netpllookup!A2:m2500,5,FALSE)"
Rng(nRow, "V").Formula = "=VLOOKUP(Q" & nRow & ",netpllookup!A2:m2500,6,FALSE)"
Rng(nRow, "W").Formula = "=VLOOKUP(Q" & nRow & ",netpllookup!A2:m2500,7,FALSE)"
Rng(nRow, "X").Formula = "=VLOOKUP(Q" & nRow & ",netpllookup!A2:m2500,8,FALSE)"
Rng(nRow, "Y").Formula = "=VLOOKUP(Q" & nRow & ",augpl!A2:m2500,2,FALSE)"
Rng(nRow, "Z").Formula = "=VLOOKUP(Q" & nRow & ",seppl!A2:m2500,2,FALSE)"
Rng(nRow, "AA").Formula = "=VLOOKUP(Q" & nRow & ",octpl!A2:m2500,2,FALSE)"
Rng(nRow, "AB").Formula = "=VLOOKUP(Q" & nRow & ",novpl!A2:m2500,2,FALSE)"
Rng(nRow, "AC").Formula = "=VLOOKUP(Q" & nRow & ",netpllookup!A2:m2500,13,FALSE)"


Rng(nRow, "AP").Formula = "=VLOOKUP(Q" & nRow & ",trliqlookup!A2:i600,2,FALSE)"
Rng(nRow, "AQ").Formula = "=VLOOKUP(Q" & nRow & ",trliqlookup!A2:i600,3,FALSE)"
Next i

End Sub

lucas
01-02-2009, 12:37 PM
Be sure to mark your thread solved using the thread tools at the top of the page. That will prevent people from wasting time looking at this thread and reading through it just to find that it has been solved.

kdigital
01-02-2009, 12:44 PM
excuse that marked solved.

Aussiebear
01-02-2009, 04:32 PM
That's significantly different from that which you initially asked for assistance with.