|
VLookup in VBA?
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(xlCellT ypeVisible).Copy Destination:=Sheets("payroll sheet").Range("B40") Sheets("PAYROLL SHEET").Range("A41:AL41").Clear Sheets("DATA").Range("H76:AR76").SpecialCells(xlCe llTypeVisible).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 |
VLookup in VBA?
Hi
For instance Sheets("DATA").Range("H76:AR76").SpecialCells(xlCe llTypeVisible).Copy Sheets("payroll sheet").Range("B41").PasteSpecial xlValues -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music " 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(xlCellT ypeVisible).Copy Destination:=Sheets("payroll sheet").Range("B40") Sheets("PAYROLL SHEET").Range("A41:AL41").Clear Sheets("DATA").Range("H76:AR76").SpecialCells(xlCe llTypeVisible).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 |
VLookup in VBA?
On Jul 19, 12:33*pm, Wigi wrote:
Hi For instance Sheets("DATA").Range("H76:AR76").SpecialCells(xlCe llTypeVisible).Copy Sheets("payroll sheet").Range("B41").PasteSpecial xlValues -- Wigihttp://www.wimgielis.be= Excel/VBA, soccer and music " 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(xlCellT ypeVisible).Copy Destination:=Sheets("payroll sheet").Range("B40") Sheets("PAYROLL SHEET").Range("A41:AL41").Clear Sheets("DATA").Range("H76:AR76").SpecialCells(xlCe llTypeVisible).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 |
VLookup in VBA?
|
VLookup in VBA?
On Jul 19, 1:26*pm, "Don Guillett" 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 wrote in message ... On Jul 19, 12:33 pm, Wigi wrote: Hi For instance Sheets("DATA").Range("H76:AR76").SpecialCells(xlCe llTypeVisible).Copy Sheets("payroll sheet").Range("B41").PasteSpecial xlValues -- Wigihttp://www.wimgielis.be=Excel/VBA, soccer and music " 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(xlCellT ypeVisible).Copy Destination:=Sheets("payroll sheet").Range("B40") Sheets("PAYROLL SHEET").Range("A41:AL41").Clear Sheets("DATA").Range("H76:AR76").SpecialCells(xlCe llTypeVisible).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 |
VLookup in VBA?
|
| All times are GMT +1. The time now is 09:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com