Question pertains to moving Excel 2003 data.

February 4, 2011 at 13:52:44
Specs: Windows XP
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

See More: Question pertains to moving Excel 2003 data.

Report •

#1
February 5, 2011 at 15:20:54
re: ...which correspond to the days of the week in target file: Track. Within Track, cell B1, contains the control formula...

If Track is a file name, there isn't really a "cell B1", at least not at the file level.

If Track is a sheet name, 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.csv is 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.csv is in a sheet named s

Put 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.


Report •

#2
February 5, 2011 at 19:18:00
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



Report •

#3
February 5, 2011 at 19:58:47
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.


Report •

Related Solutions

#4
February 7, 2011 at 10:29:33
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


Report •

#5
February 7, 2011 at 12:47:30
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.


Report •

#6
February 8, 2011 at 07:14:37
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


Report •

#7
February 8, 2011 at 07:48:26
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.


Report •

#8
February 8, 2011 at 22:32:48
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!


Report •

Ask Question