Entering Data in a Worksheet

I'm using the following code to take calculated data in VBA and send it to specific cells on an excel sheet. The code I have used causes the data to show up on the first worksheet in the excel book in the specified cells. Does anyone know if it is possibly to put the data into a specific workbook and how I would modify the code?

Cells(3, 4) = DiameterFt
Cells(4, 4) = FloorThickness
Cells(5, 4) = FootingThi

Thats a little more complicated than I thought it would be so perhaps I asked my question in an incorrect way. Let me try again.

I have four worksheets (Sheet 1, Sheet 2, Sheet 3 and Sheet 4) in my open workbook. When I perform my calculation in VBA I can easily get the data to show up on the first worksheet (Sheet 1) using the "Cells (x,y) = Variable" format. What I can't seem to do is get data into the other sheets in the open workbook. So, for example, I can get the "DiameterFT" data to show up in Cells (3,4) on Sheet 1; however, I can't get it to show up in cells (3,4) on Sheet 3. I was thinking it might be as easy as using the name of the sheet prior to the "Cells" command.

Thank you for your help and sorry that I misspoke on the worksheet versus workbook.

So, I tried it as follows but it didn't seem to work:

With Sheets("Labor")
Cells(41, 5) = BearingPads
End With

My worksheet is titled "Labor" of course and the data on the number of Bearing Pads should go into the cell described as row 41 and column 5. When I run the program, I get nothing in that cell. I have the workbook open but the worksheet is not active. Does the worksheet need to be the active worksheet?

BearingPads is a value that is calculated in the VB code itself. Perhaps it might help if I explain what I'm doing.

I'm entering values in a UserForm. Based on those input values, I'm calculating values in VB that are then inserted back into the excell workbook at a click event called "Recalculate". I would like to insert the calculated values into specific worksheets instead of all the values going straight onto the active worksheet just behind the user form.

So, in the code I displayed, I'm calculating a value in VB which is BearingPads and then attempting to insert it in a specific cell on a specific worksheet within the workbook. That worksheet is titled "Labor".

I hope I'm making sense.

This information is very helpful and seems to make good sense. In answer to your question the variable BearingPads is declared as a Double.

Again, the code you propose makes good sense to me and I like the idea of hiding the screen during updating. When I enter the code and hit the click event I get an error of Invalid Qualifier at the Range line. Any ideas on how to solve that? I looked it up in help and found the following:


Qualifiers are used for disambiguation. This error has the following cause and solution:

The qualifier does not identify a project, module, object, or a variable of user-defined type within the current scope.
Check the spelling of the qualifier. Make sure that the qualifying identifier is within the current scope. For example, a variable of user-defined type in a Private module is visible only within that module.

The entire code is in the UserForm. Could that be the issue?

No problem at all. Here is the code for the click-event. Again, the intention of the click-event is to recalculate all variables and enter certain variables into the correct worksheets. Right now, I have added your proposed code but have commented it out since it isn't working properly. I really appreciate your help. I'm very new to VB for Excel and perhaps you can give me some pointers.

Code is as follows:

'This portion of the code recalculates the sheet and inserts calculated data into the excel estimate

Private Sub Recalc_Click()

' SCREEN UPDATE - This portion of the code keeps the screen from updating until the calculation is complete

Application.ScreenUpdating = False


' SUBPART FLOOR - This subportion of the code is for calcualtion of the floor quantities based on input

' Declare all variables in the floor calculation
Dim EpoxyGrooveWidth As Double
Dim FootingWidthOut As Double
Dim FloorRadius As Double
Dim FootingCircumference As Double
Dim TransitionCircumference As Double
Dim EpoxyGrooveCircumference As Double
Dim SubgradeArea As Double
Dim FootingArea As Double
Dim TransitionArea As Double
Dim FloorArea As Double
Dim FloorVolume As Double
Dim FootingVolume As Double
Dim TransitionVolume As Double
Dim WaterStopEpoxy As Double
Dim FloorConcreteVol As Double
Dim FloorSteelWeight As Double
Dim BottomBolster As Double
Dim CaneQuantity As Double
Dim CaneHeight As Double
Dim FloorBarDia As Double
Dim FloorBarArea As Double
Dim TransCircBarDia As Double
Dim TransCircBarArea As Double
Dim TransCaneBarDia As Double
Dim TransCaneBarArea As Double
Dim FootingCircBarDia As Double
Dim FootingCircBarArea As Double
Dim FootingRadialBarDia As Double
Dim FootingRadialBarArea As Double
Dim CaneLength As Double
Dim WaterstopCirc As Double
Dim WaterstopLength As Double
Dim TransCircWeight As Double
Dim FootingRadialWeight As Double
Dim FootingCircWeight As Double
Dim BolsterQuantity As Double
Dim PolyQuantity As Double
Dim EdgeFormQuantity As Double
Dim BarTieQuantity As Double
Dim FloorBosterSpace As Double
Dim FootingRadHeight As Double
Dim FootingRadLength As Double
Dim WaterstopFormLumber As Double
Dim SeisCableNum As Double
Dim SeisCableLength As Double
Dim FoundStakes As Double
Dim EdgeFormArea As Double
Dim FloorBraceInsert As Double
Dim BearingPads As Double


'VARIABLES - Determine variables based on selected values

' Floor Bar Size and Area
If (FloorBar.Text = "#4") Then
FloorBarDia = D4Bar
FloorBarArea = A4Bar
ElseIf (FloorBar.Text = "#5") Then
FloorBarDia = D5Bar
FloorBarArea = A5Bar
Else
FloorBarDia = D6Bar
FloorBarArea = A6Bar
End If

' Transition Circumferential Bar Size and Area
If (TransCircBar.Text = "#4") Then
TransCircBarDia = D4Bar
TransCircBarArea = A4Bar
ElseIf (TransCircBar.Text = "#5") Then
TransCircBarDia = D5Bar
TransCircBarArea = A5Bar
Else
TransCircBarDia = D6Bar
TransCircBarArea = A6Bar
End If

' Transition Cane Bar Size and Area
If (TransCaneBar.Text = "#4") Then
TransCaneBarDia = D4Bar
TransCaneBarArea = A4Bar
ElseIf (TransCaneBar.Text = "#5") Then
TransCaneBarDia = D5Bar
TransCaneBarArea = A5Bar
Else
TransCaneBarDia = D6Bar
TransCaneBarArea = A6Bar
End If

' Footing Circumferential Bar Size and Area
If (FootingCircBar.Text = "#4") Then
FootingCircBarDia = D4Bar
FootingCircBarArea = A4Bar
ElseIf (FootingCircBar.Text = "#5") Then
FootingCircBarDia = D5Bar
FootingCircBarArea = A5Bar
Else
FootingCircBarDia = D6Bar
FootingCircBarArea = A6Bar
End If

' Footing Radial Size and Area
If (FootingRadialBar.Text = "#4") Then
FootingRadBarDia = D4Bar
FootingRadBarArea = A4Bar
ElseIf (FootingRadialBar.Text = "#5") Then
FootingRadBarDia = D5Bar
FootingRadBarArea = A5Bar
Else
FootingRadBarDia = D6Bar
FootingRadBarArea = A6Bar
End If


' Calculate initial dimensions and quantities
FloorBolsterSpace = 3
EpoxyGrooveWidth = 1
FootingWidthOut = FootingWidth - (12 / 12) - (CorewallBtmThick / 12) - (EpoxyGrooveWidth / 12) - ((3 / 8) / 2 / 12)
FloorRadius = (DiameterFt / 2) + (CorewallBtmThick / 12) + (EpoxyGrooveWidth / 12) + FootingWidthOut
FootingCircumference = Pi * (2 * (FloorRadius - (FootingWidth / 2)))
TransitionCircumference = Pi * (2 * FloorRadius - ((2 * FootingWidth) + (2 * (TransitionWidth * 1 / 3))))
EpoxyGrooveCircumference = 2 * (FloorRadius - FootingWidthOut - ((3 / 8) / 2 / 12) - ((EpoxyGrooveWidth / 2) / 12)) * Pi
SubgradeArea = ((Pi * (FloorRadius * 2) ^ 2) / 4)
FootingArea = FootingWidth * FootingCircumference
TransitionArea = ((TransitionWidth ^ 2 + ((FootingThickness - FloorThickness) / 12) ^ 2) ^ 0.5) * TransitionCircumference
FloorArea = (((FloorRadius * 2) - ((FootingWidth * 2) + (TransitionWidth * 2))) ^ 2 * Pi) / 4
FloorVolume = (FloorThickness / 12) * ((Pi * (FloorRadius * 2) ^ 2) / 4)
FootingVolume = FootingWidth * ((FootingThickness - FloorThickness) / 12) * FootingCircumference
TransitionVolume = ((TransitionWidth * ((FootingThickness - FloorThickness) / 12)) * TransitionCircumference) / 2
BottomBolster = FloorThickness - (2 * FloorBarDia) - FloorCover
CaneHeight = FootingThickness - FloorCover - TransCircBarDia - BottomBolster - (2 * FloorBarDia)
CaneLength = 1 + (((CaneHeight * Pi) / 2) / 12) + 0.75 + ((TransitionWidth ^ 2 + ((CaneHeight / 12) ^ 2)) ^ 0.5)
FootingRadHeight = FootingThickness - FloorCover - (FootingCircBarDia * 2) - BottomBolster - (2 * FloorBarDia)
FootingRadLength = ((FootingWidthOut - (2 / 12) - ((2.5 / 12))) * 2) + ((FootingRadHeight / 12) * 2) * ((((FloorRadius - (FootingWidthOut / 2)) * 2) * Pi) / (FootingRadialSpace / 12))
WaterstopCirc = 2 * (FloorRadius - FootingWidthOut) * Pi
SeisCableNum = SeismicCables


' Determine Allowable Stress Based on Design Basis
If (DesignBasis.Text = "AWWA D110") Then
AllowStressFac = 0.7
LossofStress = 25000
Else
AllowStressFac = 0.75
LossofStress = 25000
End If


' Calculate quantities for estimating with adjustments for waste
WaterStopEpoxy = ((EpoxyGrooveWidth / 12) * EpoxyGrooveCircumference * (4 / 12) * EpoxyWaste) * 7.48
FloorConcreteVol = (FloorVolume + FootingVolume + TransitionVolume) / 27
FloorSteelWeight = FloorVolume * (FloorPercent / 100) * wSteel * 2
CaneQuantity = ((FloorRadius - FootingWidthOut - (2.625 / 12)) * 2 * Pi) / (TransCaneSpace / 12)
CaneWeight = (CaneLength) * CaneQuantity * wSteel * (TransCaneBarArea / 144)
WaterstopLength = WaterstopCirc * 1.05
TransCircWeight = TransitionWidth * FootingThickness * TransCircPercent * wSteel
FootingRadWeight = FootingRadLength * (FootingRadBarArea / 144) * wSteel
FootingCircWeight = FootingWidthOut * (FootingThickness / 12) * ((FloorRadius - (FootingWidthOut / 2)) * 2 * Pi) * (FootingCircPercent / 100) * wSteel
WaterstopFormLumber = ((WaterstopCirc * 5.5 * 0.75) / 12) * 2
SeisCableLength = SeismicCables * 20 ' Added 20' here until we know how long they'll be
FoundStakes = FootingCircumference / 3 ' Used 3 for stakes at 36" c/c
EdgeFormArea = FootingCircumference * (FootingThickness / 12)
FloorBraceInsert = NumberPanels * 2
BearingPads = NumberPanels * 2


' DATA ENTRY - This portion of the code enters the calculated values into the correct worksheets for estimating

'ActiveWorkbook.Sheets("Labor").Activate
'Range("E15").Value = BearingPads.Value
'Range("E16").Value = FoundStakes.Value
'Range("E18").Value = FloorSteelWeight.Value

' This portion of the code is to test formulas only and will be deleted after testing

Cells(3, 4) = DiameterFt
Cells(4, 4) = FloorThickness


' SUBPART WALL AND PANELS - This subportion of the code is for calcualtion of the wall quantities based on input

' Declare all Variables in the Wall Calculation
Dim WallHeight As Double
Dim InsideBarDia As Double
Dim InsideBarArea As Double
Dim InsideBarDev As Double
Dim OutsideBarDia As Double
Dim OutsideBarArea As Double
Dim BottomCircDia As Double
Dim BottomCircArea As Double
Dim TopCircDia As Double
Dim TopCircArea As Double
Dim QInsideBar As Double
Dim WInsideBar As Double


' VARIABLES - Determine variables based on selected values

' Inside Bar Size and Area
If (InsideBar.Text = "#4") Then
InsideBarDia = D4Bar
InsideBarArea = A4Bar
InsideBarDev = HD4Bar
ElseIf (InsideBar.Text = "#5") Then
InsideBarDia = D5Bar
InsideBarArea = A5Bar
InsideBarDev = HD5Bar
Else
InsideBarDia = D6Bar
InsideBarArea = A6Bar
InisdeBarDev = HD6Bar
End If


' Outside Bar Size and Area
If (OutsideBar.Text = "None") Then
OutsideBarDia = 0
OutsideBarArea = 0
ElseIf (OutsideBar.Text = "#4") Then
OutsideBarDia = D4Bar
OutsideBarArea = A4Bar
ElseIf (OutsideBar.Text = "#5") Then
OutsideBarDia = D5Bar
OutsideBarArea = A5Bar
Else
OutsideBarDia = D6Bar
OutsideBarArea = A6Bar
End If


' Bottom Circumferential Bar Size and Area
If (BottomCirc.Text = "#4") Then
BottomCircDia = D4Bar
BottomCircArea = A4Bar
ElseIf (BottomCirc.Text = "#5") Then
BottomCircDia = D5Bar
BottomCircArea = A5Bar
Else
BottomCircDia = D6Bar
BottomCircArea = A6Bar
End If


' Top Circumferential Bar Size and Area
If (TopCirc.Text = "#4") Then
TopCircDia = D4Bar
TopCircArea = A4Bar
ElseIf (TopCirc.Text = "#5") Then
TopCircDia = D5Bar
TopCircArea = A5Bar
Else
TopCircDia = D6Bar
TopCircArea = A6Bar
End If


' Calculate initial dimensions and quantities
WallHeight = SidewallFt - ((FootingThickness - FloorThickness) / 12) - (BearingThick / 12) - (DomeLipHeight / 12)
QInsideBar = ((DiameterFt + (InsideCover / 12) + (InsideCover / 12) + (InsideBarDia / 12)) * Pi) / (InsideSpacing / 12)
WInsideBar = QInsideBar * (InsideBarArea / 144) * wSteel * (WallHeight + InsideBarDev - (2 / 12))
QOutsideBar = ((DiameterFt + (CorewallBottomThick / 12) * Pi)) / InsideSpacing


' WALL PANEL DATA - This portion of the code is to get data and test formulas only and will be deleted after testing

Cells(16, 8) = InsideBarDia
Cells(17, 8) = InsideBarArea
Cells(18, 8) = OutsideBarDia
Cells(19, 8) = OutsideBarArea


' SCREEN UPDATE - This portion of the code keeps the screen from updating until the calculation is complete

Application.ScreenUpdating = True


End Sub