Solved: New Member-Formula act within a range

[ssafiri]

Transmitted

[Bob Phillips]

Welcome to VBAX ssafiri.

I am sorry, but your question confused me. Can you post a workbook with some examples of what you want calculated.

[ssafiri]

Hello to all moderator and expert in this great site!
I'm completely new in VBA. I want to have a user form to calculate some profile weight in some structures. I create a form as attached excel file.
I bump into a two problem. First, I want to calculate weight of a cut profile with multiply its unit weight to their dimension (i.e. a row in column D into the same raw but in Column E) and the reason put in rows in column F. I want to this that when I push command button: "Enter data and calculate!" on the form Angle. As you see all data are placed in their position except a formula like this:
=A5*B5. How can I do this? It sick me a lot!!

Also you will see many lines for formatting data which will be entered to Sheet. Is there any better solution to reduce this huge writing code?

Your help will be highly appreciated.

THX.

[lucas]

threads merged

[mdmackillop]

The formula line you need is
Range("F" & LastRow).FormulaR1C1 = "=RC3/1000*RC4*RC5"

[mdmackillop]

frmAngle code

Private Sub cmdCalc_Click()
If ValidateData = True Then
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row + 1
Range("A" & LastRow).Value = "ANGLE"
Range("B" & LastRow).Value = cmbAngleType.Text
Range("C" & LastRow).Value = txtAngleLength.Text
Range("D" & LastRow).Value = cmbAngleQTY.Text
Range("F" & LastRow).FormulaR1C1 = "=RC3/1000*RC4*RC5"
Range("F" & LastRow).NumberFormat = "0.000"
With Range("A" & LastRow & ":F" & LastRow)
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlHairline
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With
ClearForm
End If
End Sub


MENU Code


Private Sub cmdOpenAngle_Click()
Application.ScreenUpdating = False
Header1
Header2
Application.ScreenUpdating = True
frmAngle.Show
End Sub
Public Sub Header1()
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row + 1
Range("A" & LastRow).Value = "Profile Type"
Range("B" & LastRow).Value = "Angle Type"
Range("C" & LastRow).Value = "Angle Length(mm)"
Range("D" & LastRow).Value = "Angle No."
Range("E" & LastRow).Value = "Angle unit Weight(Kg/m)"
Range("F" & LastRow).Value = "Weight for Each Support(Kg)"
With Range("A" & LastRow & ":F" & LastRow)
.Font.Bold = True
.EntireColumn.AutoFit
.Cells.Interior.Color = RGB(203, 200, 206)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders(xlInsideVertical).Weight = xlThin
End With
End Sub
Public Sub Header2()
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row + 1
With Range("A" & LastRow & ":F" & LastRow)
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlHairline
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.HorizontalAlignment = xlCenter
End With
End Sub

[ssafiri]

Thanks million.:beerchug:

You solved me very well.
That is the reason i sumitted in forumes in any softwares: Great Experts!!:rofl:

Thanks again.