PLEASE!

Wingz

New Member
Joined
Mar 5, 2002
Messages
33
Can someone PLEASE tell me how to make reference to a cell in a closed workbook WITHOUT using the R1C1 reference format. I need to use Cells(x,y)so I can loop.

It may just be a syntax thing but here is what I want:
activecell.value = Workbooks("C:My Documentsemployee.xls).Worksheets(1).cell(x,y).value ----> Or something to this effect. I know the syntax is off but if ANYONE can help, I would greatly appreciate it.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Ok, here goes.

I've had a look at this and your earlier post and I'm not sure why this has to be in a macro but I'm sure you have your reasons.

First things first, the full formula that needs to go in cell A1 of the active workbook is:
='C:My Documents[Employees.xls]Sheet1'!$B1&" "&'C:My Documents[Employees.xls]Sheet1'!$C1

As there is no dollar($) in front of the row reference, you could just copy and paste this formula down.

If you wanted a macro to go through and fill each in for 75 rows then how about:

Sub test ()

For x = 1 To 75
Range("A" & x).Formula = "='C:My Documents[Employees.xls]Sheet1'!B" & x & "&"" ""&'C:My Documents[Employees.xls]Sheet1'!C" & x & ""
Next x

End Sub

Is this what you had in mind??

Rgds
AJ


N.B. Where ever you see two backslashes above, only use one in your formulae/code (it's just the way this website works!)
This message was edited by AJ on 2002-04-10 13:06
 
Upvote 0
Try the following:

ActiveCell.Value = "= 'C:My Documents" & "[employee.xls]Sheet1'!" & Cells(1, 1).Address

(Remember this board adds an extra slash mark)
 
Upvote 0
AJ - Thanks for your response. The reason it has to be in a macro is because every week, the end users need to update their timesheets with the employee list. If someone has been deleted, they should bot appear in the next week. So the macro gets run 4 times in 4 weeks, pasting the records from the employee file, into the active workbook. I will try the code and hope it works. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top