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: Don Guillett on 20 Jul 2008 11:25 Glad to help. Most of us here prefer TOP posting so we don't have to scroll down for the reply -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1(a)austin.rr.com <golf4wff(a)hotmail.com> wrote in message news:1ae8f85d-e935-4316-b0fa-d915c357352f(a)79g2000hsk.googlegroups.com... 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 |