MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Create Dynamic Ranges with VB, clue given

  Asked By: Lewis    Date: Sep 15    Category: MS Office    Views: 1686

I want your help. i want to make dynamic ranges in VB.
I have a database wherein i keep adding data in rows. I want someone
to create a dynamic range which will expand as and when other cells
are filled. Example, I have data in columns A, B & C. In a2 & b2
there are amounts which are filled manually and C2 is a function,
=sum(a2*b2) as and when i fill a3 & b3 the formula in should get
exteded in C3 dynamically. Please Help me to do this with VB with
offset metthod.

One of my friend send me this clue, but i don't know how to make it
applicable to my soloution.

Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Intersect(Columns("b"), Target)
If rg Is Nothing Then Exit Sub
rg.Offset(0, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub



1 Answer Found

Answer #1    Answered By: Alberta Miller     Answered On: Sep 15

This will work straight out of the box. All you need to do is place this
code in the Worksheet module rather than a standard module. Then
whenever you enter something in column B, the formula  will turn up in C.

Didn't find what you were looking for? Find more on Create Dynamic Ranges with VB, clue given Or get search suggestion and latest updates.