Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching external file for values
Hi Folks,
I have a datapull from a propriatary db that we use and I have the ability to clean up the data, no problem. i have another workbook which is a report in a fixed format with multiple sections seperated by rows. such that rows 30 - 81 are part of a section with the section header contained in cell A29. I would like to be able to run some code that would search for the header value in the dp workbook and copy the values from the the cells directly below the header value until the next header value is reached. The dp contains the necessary data in columns A and B. All the header values are fixed in terms of value but may move in terms of cell placement throughout the year. Any help in the right direction is greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching external file for values
Try this code. May need some modifications.
Sub test() folder = "c:\temp\" dbfile = "abc.xls" Workbooks.Open Filename:=folder & dbfile Set dbBk = ActiveWorkbook Set wksht = ThisWorkbook.Sheets("Sheet1") With wksht LastRow = .Cells(Rows.Count, "A").End(xlUp).Row RowNumber = 1 Do If IsEmpty(.Range("A" & RowNumber)) Then HeaderRow = .Range("A" & RowNumber). _ End(xlDown).Row Else HeaderRow = RowNumber End If RowNumber = HeaderRow + 1 HeaderName = .Range("A" & HeaderRow) With dbBk.Sheets("sheet1") Set c = .Columns("A:A").Find(what:=HeaderName, _ LookIn:=xlValues) If Not c Is Nothing Then firstData = c.Row + 1 LastData = c.End(xlDown).Row Rows(firstData & ":" & LastData).Copy _ Destination:=wksht.Rows(RowNumber) RowNumber = wksht.Range("A" & RowNumber). _ End(xlDown).Row End If End With Loop While RowNumber <= LastRow End With End Sub "Stephen" wrote: Hi Folks, I have a datapull from a propriatary db that we use and I have the ability to clean up the data, no problem. i have another workbook which is a report in a fixed format with multiple sections seperated by rows. such that rows 30 - 81 are part of a section with the section header contained in cell A29. I would like to be able to run some code that would search for the header value in the dp workbook and copy the values from the the cells directly below the header value until the next header value is reached. The dp contains the necessary data in columns A and B. All the header values are fixed in terms of value but may move in terms of cell placement throughout the year. Any help in the right direction is greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for values | Excel Discussion (Misc queries) | |||
Copying cell values to a external file in VBA | Excel Discussion (Misc queries) | |||
External link only works for numbers -- for text values, #N/A is displayed unless linked file is open | Links and Linking in Excel | |||
searching for values and summing the corresponding values | Excel Worksheet Functions | |||
Searching values in VBA | Excel Programming |