We help IT Professionals succeed at work.

VBA REFERENCE FORMULA EXCEL

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Europe\All"

I want the region Europe to toggle, so on the excel spreadsheet I have a dropdown on cell M5 - how do I reference the cell M5 as opposed to hardcoding the vbal code with "Europe"

The same applies to a line I have

stfilename1 = str & " GB-Corp (Jan-15) - Managed Region .pdf"

Where it says GB-Corp I would like the cell N5

Many thanks!
Comment
Watch Question

Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
to reference cell M5 use
 
Cells(5,"M")
 
or
 
Cells(5,13)
 
or
 
ActiveSheet.Cells(5,13)
 
or
 
Sheets("MySheetName").cells(5,13)
 
etc.
 
For N15, use 15,14 instead of 5,13.

Author

Commented:
stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Cells(5,13)\All"

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Cells(5,""M""))\All"

Both attach no file


stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Cells(5,13)\All"

    stfilename1 = str & " GB-Corp (Jan-15) - Managed Region .pdf"
    stfilename2 = str & " GB-Corp (Jan-15) - Booked .pdf"
    stfilename3 = str & " GB-Corp (Jan-15) - Managed .pdf"

stfilename1  is blank once I change from hardcoding to above

Thanks!
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Change to

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\" & Cells(5,13) & "\All"

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\" & Cells(5,"M") & "\All"

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\" & Cells(5,13) & "\All"

Author

Commented:
Sorry to be a pain but my VB is weak

I changed to the below and they attached nothing

    stfilename1 = str & " " & Cells(15, 14) & " (Jan-15) - Managed Region .pdf"
    stfilename2 = str & " " & Cells(15, 14) & " - Booked .pdf"
    stfilename3 = str & " " & Cells(15, 14) & " - Managed .pdf"


Thanks
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
I don't know what you mean by "they attached nothing".

If after those lines you have

Msgbox stfilename1

then you will see what is there.

Author

Commented:
So line of code

stfilename1 = str & " GB-Corp (Jan-15) - Managed Region .pdf"

Recognises the file in the folder and attaches

stfilename1 = str & " " & Cells(15, 14) & " (Jan-15) - Managed Region .pdf"

Does not recognise any folder in that file

Cell N5 in the drop down in excel = GB-Corp
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Sorry - I thought you said N15.

Use cells(5,14) instead of cells(15,14).

It is in the format cells(rownumber, columnnumber).

Author

Commented:
So I could automate the date as well I amended to

 stpath = "R:\SPM\Horis Info\Horis_Project\GBM\" & Cells(5, 15) & "\Output\" & Cells(5, 13) & "\All"

Where 5,15 = Jan-15 in excel

Again the vba doesn't recognise the file now

Il uplaod
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
You need to format the date

 stpath = "R:\SPM\Horis Info\Horis_Project\GBM\" & format(Cells(5, 15),"mmm-yy") & "\Output\" & Cells(5, 13) & "\All"

This outputs

R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Europe\All
Top Expert 2015

Commented:
Seamus,

This is a part of bigger code so help me understand that this path will it changes for the respective person in the next line??

In additional can you help me understand how does the path will look like??

Saurabh...

Author

Commented:
Hi Saurabh,

Have you opened the above file?
Top Expert 2015

Commented:
And i would use rather this code..

Since assuming this can change from person to person...

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\" & Format(Cells(cell.Row, 15).Value, "mmm-yy") & "\Output\" & Cells(cell.Row, 13).Value & "\All"

Open in new window


Saurabh...

Author

Commented:
The above works perfect Saurabh

Are you able to replicate for line

stfilename1 = str & " GB-Corp (Jan-15) - Managed Region .pdf"

So GB-Corp is found in cell N5 and Jan-15 is found in cell O6

Thanks!
Top Expert 2015

Commented:
For this will use...

stfilename1 = str & " "& cells(cell.row,14).value & " (" & format(cells(Cell.row,15).value,"mmm-yy") & ") - Managed Region .pdf"

Open in new window


Again the idea to use it this way is that lets say in next row if you get a new input it will pick that input rather then picking from one cell at a time...

Saurabh...

Author

Commented:
Sorry, I might have confused things here Saurabh

From my attached file the code ran perfectly for the first recipient Zimmer, but the other recipients received no files?

Thanks
Burst-Tool-V1.xlsm
Top Expert 2015
Commented:
If that's the case then go with Philip suggestion what he said...

The Paths will be...

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\" & Format(Cells(5, 15).Value, "mmm-yy") & "\Output\" & Cells(5, 13).Value & "\All"

stfilename1 = str & " "& cells(5,14).value & " (" & format(cells(5,15).value,"mmm-yy") & ") - Managed Region .pdf"

Open in new window


My Code gives you flexibility to change this for every row..however if you want to keep them static then this will do that for you...

Saurabh..

Author

Commented:
Thanks guys!

Author

Commented:
Saurabh, im about to post a question relating to your code which you may be able to help me with!