Adding a dymanic formula to a cell using visual basic
 

Hello,

I have been trying to add a dymanic formula to a cell using visual
basic and I am running into a little difficulty. Any assistance would
be appreciated.

I attempted using the following code. The problem line is marked with
the words "Problem Line" at the end.

Thank you in advance.

--Ian Web

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Phone Numbers")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for Last Name
If Trim(Me.Txt_Last_Name.Value) = "" Then
Me.Txt_Last_Name.SetFocus
MsgBox "Please enter an Employee Last Name"
Exit Sub
End If

'check for First Name
If Trim(Me.Txt_First_Name.Value) = "" Then
Me.Txt_First_Name.SetFocus
MsgBox "Please enter an Employee First Name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Txt_Last_Name.Value
ws.Cells(iRow, 2).Value = Me.Txt_First_Name.Value
ws.Cells(iRow, 3).Value = "=CONCATENATE(A"&iRow&","", "",B"&iRow&")"
<-- Problem Line
ws.Cells(iRow, 4).Value = Me.Txt_Phone.Value
ws.Cells(iRow, 5).Value = Me.Txt_Floor.Value
ws.Cells(iRow, 6).Value = Me.Txt_Area.Value

'Sort The Columns by Last Name, First Name

ws.Columns("A:F").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal


'clear the data
Me.Txt_Last_Name.Value = ""
Me.Txt_First_Name = ""
Me.Txt_Phone = ""
Me.Txt_Floor = ""
Me.Txt_Area = ""
Me.Txt_Last_Name.SetFocus

End Sub

Adding a dymanic formula to a cell using visual basic
 

It looks like your code may be missing some & signs.

"=CONCATENATE(A"&
iRow&
","
", "
",B"&
iRow&
")"

I've broken down where your quotes start and stop which should be followed
by an &. It seems like there's something missing though. Even if I add &
where I think it should be, the formula doesn't make sense to me. But maybe
that info will shed some light.

Good luck.
Paul


"Ian Web" wrote in message
oups.com...
Hello,

I have been trying to add a dymanic formula to a cell using visual
basic and I am running into a little difficulty. Any assistance would
be appreciated.

I attempted using the following code. The problem line is marked with
the words "Problem Line" at the end.

Thank you in advance.

--Ian Web

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Phone Numbers")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for Last Name
If Trim(Me.Txt_Last_Name.Value) = "" Then
Me.Txt_Last_Name.SetFocus
MsgBox "Please enter an Employee Last Name"
Exit Sub
End If

'check for First Name
If Trim(Me.Txt_First_Name.Value) = "" Then
Me.Txt_First_Name.SetFocus
MsgBox "Please enter an Employee First Name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Txt_Last_Name.Value
ws.Cells(iRow, 2).Value = Me.Txt_First_Name.Value
ws.Cells(iRow, 3).Value = "=CONCATENATE(A"&iRow&","", "",B"&iRow&")"
<-- Problem Line
ws.Cells(iRow, 4).Value = Me.Txt_Phone.Value
ws.Cells(iRow, 5).Value = Me.Txt_Floor.Value
ws.Cells(iRow, 6).Value = Me.Txt_Area.Value

'Sort The Columns by Last Name, First Name

ws.Columns("A:F").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal


'clear the data
Me.Txt_Last_Name.Value = ""
Me.Txt_First_Name = ""
Me.Txt_Phone = ""
Me.Txt_Floor = ""
Me.Txt_Area = ""
Me.Txt_Last_Name.SetFocus

End Sub

Adding a dymanic formula to a cell using visual basic
 

ws.Cells(iRow, 3).Value = "=CONCATENATE(A"&iRow&","", "",B"&iRow&")"

s/b...
ws.Cells(iRow, 3).Value = "=CONCATENATE(A" & iRow & ",B" & iRow & ")"

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Ian Web" wrote:

Hello,

I have been trying to add a dymanic formula to a cell using visual
basic and I am running into a little difficulty. Any assistance would
be appreciated.

I attempted using the following code. The problem line is marked with
the words "Problem Line" at the end.

Thank you in advance.

--Ian Web

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Phone Numbers")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for Last Name
If Trim(Me.Txt_Last_Name.Value) = "" Then
Me.Txt_Last_Name.SetFocus
MsgBox "Please enter an Employee Last Name"
Exit Sub
End If

'check for First Name
If Trim(Me.Txt_First_Name.Value) = "" Then
Me.Txt_First_Name.SetFocus
MsgBox "Please enter an Employee First Name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Txt_Last_Name.Value
ws.Cells(iRow, 2).Value = Me.Txt_First_Name.Value
ws.Cells(iRow, 3).Value = "=CONCATENATE(A"&iRow&","", "",B"&iRow&")"
<-- Problem Line
ws.Cells(iRow, 4).Value = Me.Txt_Phone.Value
ws.Cells(iRow, 5).Value = Me.Txt_Floor.Value
ws.Cells(iRow, 6).Value = Me.Txt_Area.Value

'Sort The Columns by Last Name, First Name

ws.Columns("A:F").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal


'clear the data
Me.Txt_Last_Name.Value = ""
Me.Txt_First_Name = ""
Me.Txt_Phone = ""
Me.Txt_Floor = ""
Me.Txt_Area = ""
Me.Txt_Last_Name.SetFocus

End Sub