Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I have two workbooks. Workbook A has been filtered. I have stored in two
arrays two visibile columns. Dim serverpos As Variant Dim ieddesc As Variant dim lastpoint as Integer lastpoint = Cells(Cells.Rows.Count, "C").End(xlUp).Row serverpos = Range(Cells(2, 3), Cells(lastpoint, 3)).SpecialCells(xlVisible) ieddesc = Range(Cells(2, 5), Cells(lastpoint, 5)).SpecialCells(xlVisible) First I noticed using the watch on the serverpos variant that there is serverpos(1) .. thru serverpos(x) .... but each variant also has a sub double variant which contains the value. Why vba put the value under the double variant? serverpos(1,1) .... serverpos(x,1) That's really not a problem I don't think, but it may be taking up extra space than needed, and understaing why it did that would be helpful. Next with workbook B active, filtered, and sorted, I would like to search the visible range for serverpos(x) in coulmn(I). When I find the match I want to copy data from two cells on that row (rows 1 & 2) named (ptdesc,ptname) into the row on workbook A, which corresponds to the row that the value I am using to serach for is in. For j = 1 To lastpoint Set rng = Range("I:I").Find(serverpos(j, 1), LookAt:=xlPart) Source.Worksheets("analog").Cells(rng.Row, 2).Copy After:=Dest.Worksheets("Analogs").Cells(j + 1, 1) Source.Worksheets("analog").Cells(rng.Row, 1).Copy After:=Dest.Worksheets("Analogs").Cells(j + 1, 1) End If Next j I noticed that when I set my variants I can chose row,value,cells which sets up the array and gives me the values in the range. I can also select address, which does not give me an array but a single value which is equall to a range of address. I was thinking of creating a multi dimensional array that contains my two initialized arrays from Workbook A and two arrays containing (ptdesc,ptname), values corresponding from the match. Really at first I was thinking that when I setup my serverpos variant in workbook A that I could have somehow also stored the row for each value, so that when I found the match I could just dereference the row from the serverpos and use it to place the cells in workbook B into workbook A in the known columns of the corresponding serverpos(x). If there is a way to do that I would appreciate finding that out as well. I next thought that if I could get the offset to first visible row in workbook A which is where my first row is located I could perhaps use it in getting the data from workbook B into the apporpriate cells of workbbok A. Is that possible. I am trying to find the best solution, can you please help :) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Return filtered values into report worksheet based on filtered valueon the data worksheet | Excel Worksheet Functions | |||
Populate multi-column list in worksheet not in a UserForm | Excel Programming | |||
How do I populate a cell with data from a cell in another workshe. | Excel Discussion (Misc queries) | |||
Auto populate data from a table into cells after a match is found | Excel Worksheet Functions | |||
input value of row and column populate from another worksheet | Excel Worksheet Functions |