Greetings: Excel 2003 source file is s.csv which contains data in the range: B1-B164. This data needs to be copied into one of five columns, B5-B168 a.k.a. Mon (2), E5-E168 a.k.a. Tue (3), H, K, N5-N168 a.k.a. Fri (6), which correspond to the days of the week in target file: Track. Within Track, cell B1, contains the control formula =Weekday(Now()) which produces the #'s: 1=Sun, 2=Mon, 7=Sat. Your help in devising the EZ'est method to copy the source data into the appropriate columns in Track would be very much appreciated and welcomed. Thank you! -withoutaclue

re: ...which correspond to the days of the week in target file: Track. Within Track, cell B1, contains the control formula...If

Trackis afilename, there isn't really a "cell B1", at least not at the file level.If

Trackis asheetname, then there is indeed a cell B1.I'll assume for the purposes of this exercise that Track is a sheet name.

I'll also assume that:

1 -

s.csvis open at the time that the macro is run. If it's not, you are going to have to specify the entire path to the file in the macro.2 - The data in

s.csvis in a sheet namedsPut this code into the workbook containing Track and run it:

Sub CopyFrom_csv() Select Case Sheets("Track").Range("B1") Case 2 rng = "B5:B168" Case 3 rng = "E5:E168" Case 4 rng = "H5:H168" Case 5 rng = "K5:K168" Case 6 rng = "N5:N168" End Select With Sheets("Track").Range(rng) .Formula = "=[s.csv]s!$B1" .Copy .PasteSpecial xlPasteValues End With End Sub

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Greetings: Let me begin, DerbyDad03, by saying thank you for your help & kindness!

You are incredible. Your use of the 'case' statement (stmt) is much more

refined than a series of 'IF' stmts that was being considered.I'll do my best to respond to each of your questions / assumptions.

Yes, 'Track' is a sheet name. Please accept my apology for the ambiguity.

Question submission anxiety.Yes, 's.csv' will be open at the time that the macro is run.

Yes, Excel opens the data file 's.csv' in a sheet named 's'.

With your permission, if a fully qualified path is required, it gets added to this line: "=[s.csv]s!$B1"? Is this correct: =E:\My Data\Excel\[s.csv]s!$B1 ?

Again, with your permission, there is one other problem that needs to be addressed. Would you be willing to give 'er a go OR should the problem be

presented to the community?In any case, thank you VERY much for your help!

-withoutaclue

You have noted the correct location for the "full path formula". The best way to ensure that you get the correct and complete formula is to open s.csv and manually put in a formula in your Track sheet by selecting B1 in the s sheet to get =[s.csv]s!$B1.

Then close s.csv and the full path should appear in the Track sheet formula bar. Copy that formula into the macro (between the quotes) and you should be good to go. Oh wait...the formula will probably show $B$1 which you'll need to change to $B1 so that the formula autofills correctly.

As far as the "other problem", if it's related to this project/macro, then post it here and we'll see what we can do.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Greetings: It's related as, 'Track' is a worksheet (wks) within a workbook (wkb) 'Search.'

Search contains wks 'Daily.' Daily's range (rng) K5-K168 contains:'=If (Dx < Cx, tbl$AJ$3, If (Fx < Dx, "<=", "")) where x=5-168. [ - If 'A' - ] [ - If 'B' - ]'If A' operation

If a cell in rng contains tbl!$AJ$3 then Daily Dx '+In!Bx' is copied to Daily Cx.'If B' operation

If a cell in rng contains "<=" then Daily Dx '+In!Bx' is copied to Daily Fx.Additionally, there is something unusual occurring with blank cells. Is

there a method to see the contents of a cell in Hex, something like

=cell("contentshex", range)?Again thank you very much for all of your kindness, help & time!

-withoutaclue

I'm not sure what you asking for related to the If statements. I don't see a question related to the info you've provided. re: "

Is there a method to see the contents of a cell in Hex"=CODE(A1) will return the ASCII code for the first character in A1

=CODE(MID(A1,2,1)) will return the ASCII code for the 2nd character in A1

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Greetings: Thank you for your reply.

Please forgive the confusion. The previous post contained two (2) separate

and unrelated questions. The first, see below, if you would be so kind to help

with, as I'm struggling to write vba code to perform the nested IF operations. The second Hex question please ignore and forget.'Track' is a worksheet (wks) within a workbook (wkb) 'Search.' Search contains wks 'Daily.' Daily's range (rng) K5-K168 contains the following nested IF:

'=If (Dx < Cx, tbl$AJ$3, If (Fx < Dx, "<=", "")) where x=5-168. [ - If 'A' - ] [ - If 'B' - ]'If A' operation

If a cell in rng contains tbl!$AJ$3 then Daily Dx '+In!Bx' is copied to Daily Cx.'If B' operation

If a cell in rng contains "<=" then Daily Dx '+In!Bx' is copied to Daily Fx.Daily D5-D168 contains '+In!Bx' where x=5-168.

Tranversing the rng, i.e., K5-K168, with a loop, and trying to figure out how to manipulate VB

to achieve the above results is a challenge that I've been struggling with for

more than a year. Your help would be much appreciated.Again thank you very much for all of your kindness, help & time!

-withoutaclue

I'm still confused by your request. re: "

Daily Dx '+In!Bx' is copied to Daily Cx."What does this mean?

Daily Dx '+In!Bx'

Are you saying that you have a Nested If in K5:K168 and that based on the results of that Nested If you want VBA to copy data from Column B of Sheets(In) to either Column C or Column F of Sheets(Daily)?

Doesn't that raise the possibility of the result of the Nested If changing and thereby requiring the code to be run again?

It seems like you have some sort of circular operation going on here.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Greetings: Thanks again for your reply.

Forgive me, I'm painting with a very broad brush which relies on pseudo programming shorthand.

There are several worksheet (wks) in workbook (wbk) Search. Daily, In, & Track

are just a few.Daily Dx '+In!Bx' Daily is a wks. Dx is an abbreviated A1 reference style where the columns are labeled using the Alphabets and the rows are labeled using numbers, i.e., D is column 'D' and x=5-168 or D5-D168.

Each cell in aforementioned range, i.e., D5-D168, contains '+In!Bx' The '+' creates a link between cells in the same worksheet or workbook. In this case, it links to

wks 'In' and a range of cells where B is column 'B' and x=5-168 or B5-B168. These cells contains values which are linked into Daily column D."Are you saying that you have a series of Nested If's in Daily K5:K168 and that based on the results of those Nested If's you want visual basic code, (VBA), to copy data from Column B of Sheets(In) to either Column C or Column F of Sheets(Daily)?" Yes! A copy of the data from Column B of Sheets(In) via the link now resides in Daily Column D.

The code is stable has been in use for years, but requires a lot of time when done manually. The only feature that might appear to be a redundancy is the stop the

data makes at wks 'In' prior to being distributed to the supporting wks. 'In' is where

all the data is inspected, filtered, & verified. Seach is rather a modular, object oriented system. Each sheet has it's own function."Doesn't that raise the possibility of the result of the Nested If changing and thereby requiring the code to be run again?" Yes, the code is run on each batch of data

received throughout the day & Daily sorts it each & every time. That why an automated, i.e., visual basic subroutine, would result in a material efficiency

improvement. Your previous code, again thank you, has provided a material

reduction in errors. With your help on this problem, another material improvement

should be realized.This is my first time seeking & receiving help, & I'm VERY grateful for your kindness

and patience. Thank you, DerbyDad03!

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History