Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with this code?!?
Hi everyone:
1. I have two sheets, "Sheet1" and "Sheet2" containing data and they are in the same workbook. 2. "Sheet2" have some data from "Sheet1" but also includes different data as well. 3. I can find new data (different) data in "Sheet2" by using vlookup. however I have macro running on sheet1 to create pivot table report. I am just missing those new data from Sheet2. 4. Both "Sheet1" and "Sheet2" has data from column A to X. Common lookup column being D. Row numbers change each month. I got this code at the moment and instead of attaching all the new data in "Sheet2" to "Sheet1" it just copies all the data in "Sheet1" and paste right under the bottom in "Sheet1". Can anybody point out what is wrong with this code? I defined "Look" as Range of Column("D:X") in Sheet1. Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("Testing.xls").Names("Look").RefersToRan ge X = 1 Do While True If Cells(X, 1).Value = Empty Then Exit Do Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) 'looking up column d in sheet1, if not found there is an error so pick up all data from row to add' If (IsError(Res)) Then 'Else Fnd = Fnd + 1 For Y = 1 To 24 DataArray(Fnd, Y) = Cells(X, Y).Value Next End If X = X + 1 Loop Windows("Testing.xls").Activate Sheets("Sheet1").Select Range("A65000").End(xlUp).Select 'This is a row with data, this row + 1 is empty' X = ActiveCell.Row + 1 For Y = 1 To Fnd 'This will populate new data from sheet2 to sheet1 For Z = 1 To 24 Cells(X, Z).Value = DataArray(Y, Z) Next X = X + 1 Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with this code?!?
Windows("Testing.xls").Activate
Sheets("Sheet1").Select change the above two lines to be where you wish the data to go. Then it should work for you. "James8309" wrote: Hi everyone: 1. I have two sheets, "Sheet1" and "Sheet2" containing data and they are in the same workbook. 2. "Sheet2" have some data from "Sheet1" but also includes different data as well. 3. I can find new data (different) data in "Sheet2" by using vlookup. however I have macro running on sheet1 to create pivot table report. I am just missing those new data from Sheet2. 4. Both "Sheet1" and "Sheet2" has data from column A to X. Common lookup column being D. Row numbers change each month. I got this code at the moment and instead of attaching all the new data in "Sheet2" to "Sheet1" it just copies all the data in "Sheet1" and paste right under the bottom in "Sheet1". Can anybody point out what is wrong with this code? I defined "Look" as Range of Column("D:X") in Sheet1. Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("Testing.xls").Names("Look").RefersToRan ge X = 1 Do While True If Cells(X, 1).Value = Empty Then Exit Do Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) 'looking up column d in sheet1, if not found there is an error so pick up all data from row to add' If (IsError(Res)) Then 'Else Fnd = Fnd + 1 For Y = 1 To 24 DataArray(Fnd, Y) = Cells(X, Y).Value Next End If X = X + 1 Loop Range("A65000").End(xlUp).Select 'This is a row with data, this row + 1 is empty' X = ActiveCell.Row + 1 For Y = 1 To Fnd 'This will populate new data from sheet2 to sheet1 For Z = 1 To 24 Cells(X, Z).Value = DataArray(Y, Z) Next X = X + 1 Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
could someone look at my code please and tell me what's wrong? tha | Excel Programming | |||
what is wrong with this code? | Excel Programming | |||
wrong code? | Excel Programming | |||
What's wrong with my code ? | Excel Worksheet Functions | |||
What's wrong with the code,pls hv a look | Excel Programming |