novice VBA syntax question

ben4

New Member
Joined
Jul 11, 2008
Messages
33
(let me apologize in advance for not using ANY of the proper lingo...)

Can anyone offer guidance on if it's possible to use defined cell names in formula codes instead of variables?

For instance, if I have a function with a defined term of StartWeek, ... in the line:

ActiveCell.Offset(2, 0).Range("A1").Select

would it be possible to replace the variable coordinates "Offset(2,0)" with my defined "StartWeek"? I'm trying to write a function that I can call up in any cell , horizontally or vertically, but will always look to the same locked cell position for a variable.

If you can make sense of this question, you're help will be greatly appreciated.
Thanks!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
ben4<SCRIPT type=text/javascript> vbmenu_register("postmenu_1621672", true); </SCRIPT>

Function ? to select other cell ?

What are you trying to do ?
 
Upvote 0
Welcome to the board, Ben.

If I'm understanding your question correctly, you just put the name inside the RANGE() method.
Code:
Range("StartWeek").Offset(2,4).Interior.Color = vbBlue


<HR>
Hi Jindon -I trust you've been well.
 
Last edited:
Upvote 0
Or, in reading this again, you might want something like:
Code:
activecell.offset(range("startweek").value, 2).interior.color = vbRed
 
Upvote 0
Or, in reading this again, you might want something like:
Code:
activecell.offset(range("startweek").value, 2).interior.color = vbRed
Hi Greg,

I thought this one is what OP was after, though I'm concern about "Function".
 
Upvote 0
here is what I'm ultimately trying to do...

I'm trying to write a VBA function that will populate a weekly cashflow with different monthly payments. I have columns of information on the left and a calendar laid out across the top rows (row 1 is a week number [WeekNumber] of my calendar [1, 2, 3, etc.] and row 2 is the week ending date [mm/dd/yyyy] [Date]). in my columns to the left I have information corresponding to the week of the cashflow on which the payment should start (StartWeek), then a column for the week of the cashflow on which the payment should end (StopWeek), and a column for the amount of the payment (Rate).

Then I have a blank matrix that I want to populate with payment amounts on the weeks in which they belong, and zero where no payment is necessary. I'm looking for a (formula or function or macro??) that can First:
Evaluate the first week in which the payment is due (ie, I'm in cell G10. StartWeek in cell A10 equals 5. WeekNumber in G1 is 4, therefor enter "0" in G10. When I paste the same formula in H10, A10 still equals 5, and H1 equals 5 as well, so enter Rate in H10.)

That's the first part. Then...
I want to make the recurring payment in the same week of the month, for all subsequent months, until the payment is supposed to stop. (ie, the Date in H10 was 7/13/2008, so for this particular payment, I want to enter a Rate in the cashflow every time I hit the 2nd week of each subsequent month - I10 corresponds to 7/20 - do nothing, J10 corresponds to 7/27 - do nothing, K10 corresponds to 8/3 - do nothing, L10 corresponds to 8/10 [2nd wk of the month!!] - Enter Rate).

Then this would go on until it reaches a column where StopWeek = WeekNumber, in which case it would enter the last payment (if the column corresponds to the appropriate week of the month as determined by initial StartWeek) then begin to return "0" for any WeekNumber > StopWeek, or just return "0" if StopWeek=WeekNumber but Date is not the proper week of the month for a payment, and "0" thereafter.

does that make sense and is any of this possible? Or is there an easier way to accomplish what I'm trying to do?
Thanks!
 
Upvote 0
Oofda - ok. That's quite a bit to try and visualize. I would suggest you try to use Colo's HTML maker or ExcelJeanie's add-in to post a sample. We have a test forum if you need to practice. And don't go overboard and post beaucoup cells. Post enough for members to understand what you need, but nothing more.

I have a hunch that you may not need a whole lot of VBA. If you describe your problem well enough, it might be possible to do what you want using naught but built-in formulae.
 
Upvote 0
Here is a sample of the type of table I'd be working with - only with MANY more Accounts and for a much longer schedule
Book4.xls
ABCDEFGHIJKL
1PrepWkPrepWkPrepWkPrepWkPrepWkPrepWkPrepWkPrepWk
2STARTSTOP12345678
3WKWKRATE7/10/20087/17/20087/24/20087/31/20088/7/20088/14/20088/21/20088/28/2008
4Acct11613
5Acct251013
6Acct331013
7Acct42813
8Acct54513
Sheet1


And this is what this table would like like filled in (just so you understand the setup here...)
Book4.xls
ABCDEFGHIJKL
1PrepWkPrepWkPrepWkPrepWkPrepWkPrepWkPrepWkPrepWk
2STARTSTOP12345678
3WKWKRATE7/10/20087/17/20087/24/20087/31/20088/7/20088/14/20088/21/20088/28/2008
4Acct116$13.00$13.00$-$-$-$-$13.00$-$-
5Acct2510$13.00$-$-$-$-$13.00$-$-$-
6Acct3310$13.00$-$-$13.00$-$-$-$-$13.00
7Acct428$13.00$-$13.00$-$-$-$-$13.00$-
8Acct545$13.00$-$-$-$13.00$-$-$-$-
Sheet1


If this helps anyone make sense of my earlier post and they'd like to offer any suggestions, I'd love to hear it.
Thanks!!
 
Upvote 0
That would be...
Code:
Sub test()
Dim r As Range
For Each r In Range("b4", Range("b" & Rows.Count).End(xlUp))
    Union(r.Offset(,r.Value + 2), r.Offset(,r.Offset(,1).Value + 2).Value = r.Offset(,2).Value
Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
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