Thread: VBA Question: Formulas and Range variables

If you allow the user to select the range, I would think that you would have to
verify that the selection made sense.

What happens if I selected X99:Z105, Iv3:Iv8

Instead, maybe you could drop the selection process and just plop the formula
into the range based on the data in the worksheet.

Dim Wks as worksheet
Dim LastRow as long

set wks = worksheets("Sheet1") 'activesheet???

with wks
'checking the last used cell in column A to get the lastrow
lastrow = .cells(.rows.count,"A").end(xlup).row

.range("C2:C" & lastrow).formula = "=COS(a2)*cos(b2)"
End with

===
Excel will adjust the formulas--just like when you autofill the range manually.

CratoDefrog wrote:
>
> I've got a good background in Visual Basic, but am new to VBA for Excel.My
> question is, if I have 2 ranges of data, how do I assigne a formula to a
> third range? Here is a simplifed example of what I'm trying to do.
>
> A B C
> --------------
> 12 | 23 | formula using A1 & B1 values
> 21 | 24 | same formula using A2 & B2 values
>
> I'm using the following code to select an entire column (not very efficent,
> but it's day one of VBA for Excel for me):
>
> Dim RangeA As Range
> Set RangeA = Application.InputBox _
> (prompt:="Select Device Identifier Range:", _
> Title:="Device Identifier:", _
> Default:=Selection.Address, _
> Type:=8)
>
> This returns (in the input box) a range like $A$1
> I do this for columns A, B and C.
>
> Can you assign a formula over an entire range?
> I've tryed a few things like:
>
> Dim sformula as string
> sformula= "=COS(RangeA) * COS (RangeB)"
> RangeC.Formula=sformula
>
> This kicks out into the On Error handler.
>
> I'm probably doing something pretty stupid, but any enlightenment would be
> greatly appreciated.

--

Dave Peterson

Thanks! I've learned something here. That cleared up one question on using
range variables.

The only problem is this macro will have to be used with spreadsheets that
come in from sales weasels and field service teams and they invariably will
have different column structures. The only thing they will have in common
is that they will have, somewhere in them, two sets of latititude/longitude
pairs and I need to find an empty column to perform the distance
calculations.

Actually, we want to make THEM do the calculations, Some of them don't have
a very good understang of Excel.
Unfortunately, I have to include myself in that catagory since my
background is VB programming and programming for sql-based GIS databases.
However, it is turning into an interesting exercise

Thank you for your response!


Dave Peterson <petersod@verizonXSPAM.net> wrote in
news:4B97C63D.3627CBAA@verizonXSPAM.net:

> If you allow the user to select the range, I would think that you
> would have to verify that the selection made sense.
>
> What happens if I selected X99:Z105, Iv3:Iv8
>
> Instead, maybe you could drop the selection process and just plop the
> formula into the range based on the data in the worksheet.

If the worksheets have common titles in a row (row 1???), you could search for
those two columns. They have to be unique titles, too.

Then you could insert a new column A or even stick it next to one of those
columns or stick it at the end.

CratoDefrog wrote:
>
> Thanks! I've learned something here. That cleared up one question on using
> range variables.
>
> The only problem is this macro will have to be used with spreadsheets that
> come in from sales weasels and field service teams and they invariably will
> have different column structures. The only thing they will have in common
> is that they will have, somewhere in them, two sets of latititude/longitude
> pairs and I need to find an empty column to perform the distance
> calculations.
>
> Actually, we want to make THEM do the calculations, Some of them don't have
> a very good understang of Excel.
> Unfortunately, I have to include myself in that catagory since my
> background is VB programming and programming for sql-based GIS databases.
> However, it is turning into an interesting exercise
>
> Thank you for your response!
>
> Dave Peterson <petersod@verizonXSPAM.net> wrote in
> news:4B97C63D.3627CBAA@verizonXSPAM.net:
>
> > If you allow the user to select the range, I would think that you
> > would have to verify that the selection made sense.
> >
> > What happens if I selected X99:Z105, Iv3:Iv8
> >
> > Instead, maybe you could drop the selection process and just plop the
> > formula into the range based on the data in the worksheet.

--

Dave Peterson

Dave Peterson <petersod@verizonXSPAM.net> wrote in
> If you allow the user to select the range, I would think that you
> would have to verify that the selection made sense.


Ah, I forgot... and the most important reason for the way I'm trying to do
it this way.

"My boss said to do it that way..."

LOL, anyone else been there?