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!
Seamus2626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author 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 ConsultantCommented:
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"
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Seamus2626Author 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 ConsultantCommented:
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.
Seamus2626Author 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 ConsultantCommented:
Sorry - I thought you said N15.

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

It is in the format cells(rownumber, columnnumber).
Seamus2626Author 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
Seamus2626Author Commented:
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Saurabh Singh TeotiaCommented:
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...
Seamus2626Author Commented:
Hi Saurabh,

Have you opened the above file?
Saurabh Singh TeotiaCommented:
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...
Seamus2626Author 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!
Saurabh Singh TeotiaCommented:
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...
Seamus2626Author 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
Saurabh Singh TeotiaCommented:
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..
Seamus2626Author Commented:
Thanks guys!
Seamus2626Author Commented:
Saurabh, im about to post a question relating to your code which you may be able to help me with!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.