Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.charting
ers
 
Posts: n/a
Default Help with copying dynamic column selected based on remote cell value and dynamic formula fill

Before posting my nightmare I would like to direct my thanks to all of
you who posted answers before. Browsing trough the response I complete
maybe 80% of my task in a very neat way. The remaining 20% of the task
it seems that will require more than 80% of the time!

There are 3 worksheets named S1, S2, and S3.
I use a macro to first fill the no value NA() a range sayA1:C700 in S3!
And second to open a text file and format data in it (get rid of empty
rows via sorting since data in first column is ascending, etc). At the
end of this macro there is another worksheet named P1.txt which has 12
columns and a variable number of rows (never exciding 700).

I need to copy only 3 columns from P1.txt! into S3! in A,B and C
column. Since there are several formats I get my useful 3 column of
data not necessarily in the same place all the time. In S1! there are 3
cells (A1, A2, A3) which hold the info regarding the order where my
columns are in the P1.txt file..

One solution would be: select a range using OFFSET with P1!A1 as
reference, 0 for row, S1!$A$1-1 for column shift. Use COUNT to get to
the bottom. There is select_active_column code which I know is working
but I can not get to the first cell of the column of interest. Not to
mention I cannot convince COUNT to count a generic column!

I tried to move to the cell on top using:

///////////////////////////
Sub Macro3()
' Sheets("S3").Select
Range("a1").As Range
Range("a1").Offset(0, S1!A1).Select
End Sub
////////////////I got: argument not optional


Here is the second question:
Once I have the data in column A-C in S3! I like to run in adjacent D-G
formula which will extend down to the last row of data (again: the
length of the column varies). I like to have this formulas in a macro
rather than first cell.
They are just plain trigonometry across the row like (in S3!):
D1=B1+(S1!$B$1*sin(A1+90)*PI()/180) and so on...( S1!$B$1 is another
parameter hold on S1!)
Can be that done using dynamic range?
Then is the neat part from S2! which is populated with
=IF(S1!$C$1=True,S3!,NA()), S1!$C$1 is a check box (thank you people!)
the user can plot or shut down that curve on a chart.

To resume:
1.I need help with a code to select a cell (or even better the whole
non empty column) from a sheet based on the value of a cell from
another sheet, which indicate the order of column of interest.
2. I need a code to fill a formula in the next column(s) based on
values across the row from the first 3 columns. All the columns has the
same number of rows, but the number of rows changes each time the data
is updated.

I apologize for rather lengthy post, and I will appreciate any help,
Emil

Reply
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



All times are GMT +1. The time now is 12:03 PM.

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

About Us

"It's about Microsoft Excel"