LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default populate column of filtered worksheet A with data found in workshe

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return filtered values into report worksheet based on filtered valueon the data worksheet dicko1 Excel Worksheet Functions 1 April 21st 09 12:27 AM
Populate multi-column list in worksheet not in a UserForm Afzal Excel Programming 2 June 12th 08 11:50 PM
How do I populate a cell with data from a cell in another workshe. holtzy24 Excel Discussion (Misc queries) 1 March 11th 08 11:22 PM
Auto populate data from a table into cells after a match is found CrashOz Excel Worksheet Functions 5 January 15th 08 11:43 AM
input value of row and column populate from another worksheet Avidan Excel Worksheet Functions 1 August 21st 07 12:36 AM


All times are GMT +1. The time now is 04:16 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"