VLookup in VBA?
in [Programming]
|
Prev: Using VSTO2003:Excel 2003 appears and then disappears immediately when debugging with VS
Next: Convert Advanced filter macro to VBA
From: golf4wff on 19 Jul 2008 15:02 Hi, Everyone - I've been gone for a while, but back now... :) I have a question that I've been going crazy. I'm working on our Payroll Sheet for work, and the situation is the following: On the sheet DATA, I've put together VLookup table with all the employees and their specific data on budget percentages on hours worked. For example, one row would include Sally Smith's name, dept., an amount equal to 50% of hours worked in Program A, 50% of hours worked in Program B, etc. With some great help, I've come up with the following code: Private Sub CommandButton3_Click() If Sheets("DATA").Range("A1").Value = 4 Then Sheets("PAYROLL SHEET").Range("A40:AL40").Clear Sheets("DATA").Range("B6:V6").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("payroll sheet").Range("B40") Sheets("PAYROLL SHEET").Range("A41:AL41").Clear Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("payroll sheet").Range("B41") End If End Sub The code seems to copy the data from the table fine, but because I have some formulas in the table instead of just values, I'm getting #REF entries in the copied data. My question is: is there a way to modify my code so that the formula results (resulting values), in the tables, gets copies as to get rid of the #REF errors? Maybe using a PasteSpecial method in the code? Thanks for your help. Frank
From: Wigi on 19 Jul 2008 15:33 Hi For instance Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy Sheets("payroll sheet").Range("B41").PasteSpecial xlValues -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "golf4wff(a)hotmail.com" wrote: > Hi, Everyone - > > I've been gone for a while, but back now... :) > > I have a question that I've been going crazy. I'm working on our > Payroll Sheet for work, and the situation is the following: > > On the sheet DATA, I've put together VLookup table with all the > employees and their specific data on budget percentages on hours > worked. For example, one row would include Sally Smith's name, dept., > an amount equal to 50% of hours worked in Program A, 50% of hours > worked in Program B, etc. > > With some great help, I've come up with the following code: > > Private Sub CommandButton3_Click() > If Sheets("DATA").Range("A1").Value = 4 Then > Sheets("PAYROLL SHEET").Range("A40:AL40").Clear > Sheets("DATA").Range("B6:V6").SpecialCells(xlCellTypeVisible).Copy > Destination:=Sheets("payroll sheet").Range("B40") > Sheets("PAYROLL SHEET").Range("A41:AL41").Clear > Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy > Destination:=Sheets("payroll sheet").Range("B41") > > End If > End Sub > > The code seems to copy the data from the table fine, but because I > have some formulas in the table instead of just values, I'm getting > #REF entries in the copied data. > > My question is: is there a way to modify my code so that the formula > results (resulting values), in the tables, gets copies as to get rid > of the #REF errors? Maybe using a PasteSpecial method in the code? > > Thanks for your help. > > Frank >
From: golf4wff on 19 Jul 2008 15:55 On Jul 19, 12:33 pm, Wigi <W...(a)discussions.microsoft.com> wrote: > Hi > > For instance > > Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy > Sheets("payroll sheet").Range("B41").PasteSpecial xlValues > > -- > Wigihttp://www.wimgielis.be= Excel/VBA, soccer and music > > > > "golf4...(a)hotmail.com" wrote: > > Hi, Everyone - > > > I've been gone for a while, but back now... :) > > > I have a question that I've been going crazy. I'm working on our > > Payroll Sheet for work, and the situation is the following: > > > On the sheet DATA, I've put together VLookup table with all the > > employees and their specific data on budget percentages on hours > > worked. For example, one row would include Sally Smith's name, dept., > > an amount equal to 50% of hours worked in Program A, 50% of hours > > worked in Program B, etc. > > > With some great help, I've come up with the following code: > > > Private Sub CommandButton3_Click() > > If Sheets("DATA").Range("A1").Value = 4 Then > > Sheets("PAYROLL SHEET").Range("A40:AL40").Clear > > Sheets("DATA").Range("B6:V6").SpecialCells(xlCellTypeVisible).Copy > > Destination:=Sheets("payroll sheet").Range("B40") > > Sheets("PAYROLL SHEET").Range("A41:AL41").Clear > > Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy > > Destination:=Sheets("payroll sheet").Range("B41") > > > End If > > End Sub > > > The code seems to copy the data from the table fine, but because I > > have some formulas in the table instead of just values, I'm getting > > #REF entries in the copied data. > > > My question is: is there a way to modify my code so that the formula > > results (resulting values), in the tables, gets copies as to get rid > > of the #REF errors? Maybe using a PasteSpecial method in the code? > > > Thanks for your help. > > > Frank- Hide quoted text - > > - Show quoted text - Hi, Wigi - Thanks so much for the help!!! That did the trick. Can I ask you a few more follow-up questions? I have both the table cells and the destination cells formatted as 17 pitch font and centered/wrapped, but when the data is copied, it seems to reformat the destination cells as 10 pitch font and left-justified/ no wrap. Is there a way to modify my code to correct this? My second question is that, when the data is copied, I look at the table and it appears that the data that WAS copied is still surrounded by the "marching ants": (ya know when you copy something?) Is there a way to revise my code to reflect that, once the data is copied over, the ants around the copied data goes away? THANKS SO MUCH FOR THE HELP!!! Frank
From: Don Guillett on 19 Jul 2008 16:26 with Sheets("payroll sheet").Range("B41") .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats end with application.cutcopymode=false -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1(a)austin.rr.com <golf4wff(a)hotmail.com> wrote in message news:ca771704-4080-47db-8619-a26038c05908(a)i76g2000hsf.googlegroups.com... On Jul 19, 12:33 pm, Wigi <W...(a)discussions.microsoft.com> wrote: > Hi > > For instance > > Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy > Sheets("payroll sheet").Range("B41").PasteSpecial xlValues > > -- > Wigihttp://www.wimgielis.be= Excel/VBA, soccer and music > > > > "golf4...(a)hotmail.com" wrote: > > Hi, Everyone - > > > I've been gone for a while, but back now... :) > > > I have a question that I've been going crazy. I'm working on our > > Payroll Sheet for work, and the situation is the following: > > > On the sheet DATA, I've put together VLookup table with all the > > employees and their specific data on budget percentages on hours > > worked. For example, one row would include Sally Smith's name, dept., > > an amount equal to 50% of hours worked in Program A, 50% of hours > > worked in Program B, etc. > > > With some great help, I've come up with the following code: > > > Private Sub CommandButton3_Click() > > If Sheets("DATA").Range("A1").Value = 4 Then > > Sheets("PAYROLL SHEET").Range("A40:AL40").Clear > > Sheets("DATA").Range("B6:V6").SpecialCells(xlCellTypeVisible).Copy > > Destination:=Sheets("payroll sheet").Range("B40") > > Sheets("PAYROLL SHEET").Range("A41:AL41").Clear > > Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy > > Destination:=Sheets("payroll sheet").Range("B41") > > > End If > > End Sub > > > The code seems to copy the data from the table fine, but because I > > have some formulas in the table instead of just values, I'm getting > > #REF entries in the copied data. > > > My question is: is there a way to modify my code so that the formula > > results (resulting values), in the tables, gets copies as to get rid > > of the #REF errors? Maybe using a PasteSpecial method in the code? > > > Thanks for your help. > > > Frank- Hide quoted text - > > - Show quoted text - Hi, Wigi - Thanks so much for the help!!! That did the trick. Can I ask you a few more follow-up questions? I have both the table cells and the destination cells formatted as 17 pitch font and centered/wrapped, but when the data is copied, it seems to reformat the destination cells as 10 pitch font and left-justified/ no wrap. Is there a way to modify my code to correct this? My second question is that, when the data is copied, I look at the table and it appears that the data that WAS copied is still surrounded by the "marching ants": (ya know when you copy something?) Is there a way to revise my code to reflect that, once the data is copied over, the ants around the copied data goes away? THANKS SO MUCH FOR THE HELP!!! Frank
From: golf4wff on 19 Jul 2008 19:56
On Jul 19, 1:26 pm, "Don Guillett" <dguille...(a)austin.rr.com> wrote: > with Sheets("payroll sheet").Range("B41") > .PasteSpecial Paste:=xlPasteValues > .PasteSpecial Paste:=xlPasteFormats > end with > > application.cutcopymode=false > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguille...(a)austin.rr.com<golf4...(a)hotmail.com> wrote in message > > news:ca771704-4080-47db-8619-a26038c05908(a)i76g2000hsf.googlegroups.com... > On Jul 19, 12:33 pm, Wigi <W...(a)discussions.microsoft.com> wrote: > > > > > > > Hi > > > For instance > > > Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy > > Sheets("payroll sheet").Range("B41").PasteSpecial xlValues > > > -- > > Wigihttp://www.wimgielis.be=Excel/VBA, soccer and music > > > "golf4...(a)hotmail.com" wrote: > > > Hi, Everyone - > > > > I've been gone for a while, but back now... :) > > > > I have a question that I've been going crazy. I'm working on our > > > Payroll Sheet for work, and the situation is the following: > > > > On the sheet DATA, I've put together VLookup table with all the > > > employees and their specific data on budget percentages on hours > > > worked. For example, one row would include Sally Smith's name, dept., > > > an amount equal to 50% of hours worked in Program A, 50% of hours > > > worked in Program B, etc. > > > > With some great help, I've come up with the following code: > > > > Private Sub CommandButton3_Click() > > > If Sheets("DATA").Range("A1").Value = 4 Then > > > Sheets("PAYROLL SHEET").Range("A40:AL40").Clear > > > Sheets("DATA").Range("B6:V6").SpecialCells(xlCellTypeVisible).Copy > > > Destination:=Sheets("payroll sheet").Range("B40") > > > Sheets("PAYROLL SHEET").Range("A41:AL41").Clear > > > Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy > > > Destination:=Sheets("payroll sheet").Range("B41") > > > > End If > > > End Sub > > > > The code seems to copy the data from the table fine, but because I > > > have some formulas in the table instead of just values, I'm getting > > > #REF entries in the copied data. > > > > My question is: is there a way to modify my code so that the formula > > > results (resulting values), in the tables, gets copies as to get rid > > > of the #REF errors? Maybe using a PasteSpecial method in the code? > > > > Thanks for your help. > > > > Frank- Hide quoted text - > > > - Show quoted text - > > Hi, Wigi - > > Thanks so much for the help!!! That did the trick. Can I ask you a few > more follow-up questions? > > I have both the table cells and the destination cells formatted as 17 > pitch font and centered/wrapped, but when the data is copied, it seems > to reformat the destination cells as 10 pitch font and left-justified/ > no wrap. Is there a way to modify my code to correct this? > > My second question is that, when the data is copied, I look at the > table and it appears that the data that WAS copied is still surrounded > by the "marching ants": (ya know when you copy something?) Is there a > way to revise my code to reflect that, once the data is copied over, > the ants around the copied data goes away? > > THANKS SO MUCH FOR THE HELP!!! > > Frank- Hide quoted text - > > - Show quoted text - Hi, Don - Thanks for the guidance - THAT DID THE TRICK!!! Thanks again. Frank |