Use cell value in VBA code

Scrubbrush

New Member
Joined
Jan 2, 2017
Messages
3
Hi -

I'm a veteran Excel guy but a newby to VBA and I know this should be simple. I'm pretty good a googling VBA stuff and figuring out macros but I'm stuck on a simple one. I've checked all the Mr. Excel forms i could find on related subjets.

Background: I have a sheet with client information. Each row has a different client and each column has a datapoint about that client. There are about 100 rows and 16 columns. The client data starts on row 9, column D.

Goal: I need Excel to: 1) open a pre-formatted (existing) PDF form, 2) fill in the client name and other info from the "first row", 3) "save-as" the PDF into a specified folder, 4) close the PDF, 5) open a new blank PDF form, 6) populate the client info from the next line, 7) repeat this process until the "last row". I have gotten all of this to work fine, except...

Issue I need help with: I would like to be able to define which row (of the 100 client-rows) is the "first row" and the "last row". I would like to able to type the number for the "first row" in cell E6 and the number of the "last row" in E7 (on the same page as the client data, above the table containing client data) so I can choose to fill out forms for, say, client-rows 50 through 60 by typing 50 into E6 and 60 into E7.

Current status: I have everything working. For example, I can create the PDFs for the first four clients using this code:
For ClientRow = 9 To 12

I can change range of client-data-rows that will fill the PDF by changing the numbers in the above code. It does everything I mentioned in "Goals" above.

All I need help with is code that will replace the "9" and "12" above with the value found in cell E6 ("first row") and E7 ("last row")

I'd like to call the "first row" "FirstClient" and the"last row" "LastClient" but i can never quite figure out the "dim" and "set to" and "FirstClient = " stuff...

I hope that all makes sense. It's a long way of asking a simple question.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
VBA Code:
Dim FirstClient as Long, LastClient as Long
FirstClient = Sheets("Your Sheet").Range("E6").Value
LastClient = Sheets("Your SHeet").Range("E7").Value
 
Upvote 0
Solution
Oh good grief. I actually already had 96% of that.
the issue was that i'm an idiot.
i had this, because i copied/pasted and forgot to change "first" to "last". All i had to do was fix that.
FirstStop = Sheets("Sheet1").Range("E6").Value
FirstStop = Sheets("Sheet1").Range("E7").Value
Thank you SO much. I was losing my mind.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

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