Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Excel VBA

Resolved · Low Priority · Version 2003

Reply Wed 27 Apr 2011, 12:30Delegate Raman said...

Raman has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Excel VBA Intro Intermediate course
Excel VBA Intro Intermediate course

Excel VBA

Hi, I have a query on writing SumIf & VLookups code, where the starting point of Data is know, but number of rows could vary for e.g. Number of rows could vary between 15 and 20.

VLookups:
Is it possible to set a dynamic Table Array, where starting point is know but number of rows could vary. I tried using CurrentRegion, but got an error.

SumIfs:
If starting points of Range (with Criteria) and Sum range are known but rows could vary. Would using the code below work:

Range with Criteria:
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

Range with Criteria:
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select

Would CurrentRegion.Rows work?

Regards,
Raman

For upcoming training course dates see: Pricing & availability

Reply Wed 4 May 2011, 09:32Trainer Anthony said...

RE: Excel VBA

Hi Ramon, thanks for your query. You are on the right track! Dynamic Named Ranges are extremely useful when you have a fluctuating amount of data but rather difficult to explain simple on this forum. Here is a link to a walkthrough and prepare to get to know the OFFSET function very well...

http://www.ozgrid.com/Excel/DynamicRanges.htm

Hope this helps,

Anthony

Tue 10 May 2011: Automatically marked as resolved.

Excel articles

· Basic Excel Training
· Discover The PMT, IPMT And PPMT Functions In Excel
· Correct In, Garbage Out
· A Brief Glimpse At Excel's What If Analyses Tools
· Tips To Help You Excel

Please browse our web site to find out more about
excel computer training and other Microsoft training courses.

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips