VBA Code to post numbers instead of text into excel spread sheet

[miricleman]

Hi and Thank you in advance for taking the time to help with this? I have tried several of the methods show online on how to do this but I can not seem to find a way of inserting it into my existing code and to get it to work. **** Here is My Problem*** when I hit the Submit button on my form it run through a coded sequence to pull the information from the form to and post it into a row in excel. There are several textboxes some hold Text most of them hold numbers. I would like the number to be represented as numbers when they are posted into the spread sheet. ***Here is my code (sorry it is long)***Private Sub UploadInfo_Click()
DTPicker1 = Format(Date, "dd/mm/yyyy")

If DTPicker1 = "" Then
MsgBox ("You Must Input Value for Date.")
Exit Sub
End If

If ProductCombo = "" Then
MsgBox ("You Must Input a Product Description for the Product this Relates too.")
Exit Sub
End If


If Cypher = "" Then
MsgBox ("You Must Input a Cypher Number.")
Exit Sub
End If

If StaffName = "" Then
MsgBox ("You Must Input a Name for the Person Entering the Information.")
Exit Sub
End If

If BagLoss = "" Then
MsgBox ("You Must Input a Figure into the Bag Loses Box.")
Exit Sub
End If

If FatRef = "" Then
MsgBox ("You Must Enter a Fat Reference.")
Exit Sub
End If

If ProtRef = "" Then
MsgBox ("You Must Enter a Protein Reference.")
Exit Sub
End If

If FatNIR = "" Then
MsgBox ("You Must Enter a Fat NIR Reference.")
Exit Sub
End If

If ProtNIR = "" Then
MsgBox ("You Must Enter a Protein NIR Reference.")
Exit Sub
End If

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "!!!Please Verify Data is Entered Correctly!!! Once posted data cannot be corrected"
Style = vbYesNo + vbCritical + vbDefaultButton2
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
GoTo 10
Else
GoTo 20
End If


10
'Dim rngSearch As Range
'Dim k As Range

'Set rngSearch = Sheets("log").Range("A3:A9999")

'For Each k In rngSearch.Cells
' If k.Value = DTPicker1.Value Then
' GoTo 888
' Exit Sub
' End If
'Next


a = Sheets("Log").Range("aindex")


'Next CellCount

Sheets("Log").Range("c2").Offset(a, 0) = DTPicker1
Sheets("Log").Range("B2").Offset(a, 0) = StaffName
Sheets("Log").Range("D2").Offset(a, 0) = ProductCombo
Sheets("Log").Range("a2").Offset(a, 0) = Cypher

Sheets("Log").Range("e2").Offset(a, 0) = Unit1
Sheets("Log").Range("f2").Offset(a, 0) = Fat1
Sheets("Log").Range("g2").Offset(a, 0) = Protein1
Sheets("Log").Range("h2").Offset(a, 0) = CurdCombo1
Sheets("Log").Range("i2").Offset(a, 0) = pH1
Sheets("Log").Range("j2").Offset(a, 0) = Mixability1
Sheets("Log").Range("k2").Offset(a, 0) = Comments1

Sheets("Log").Range("l2").Offset(a, 0) = Unit2
Sheets("Log").Range("m2").Offset(a, 0) = Fat2
Sheets("Log").Range("n2").Offset(a, 0) = Protein2
Sheets("Log").Range("o2").Offset(a, 0) = CurdCombo1
Sheets("Log").Range("p2").Offset(a, 0) = pH1
Sheets("Log").Range("q2").Offset(a, 0) = Mixability1
Sheets("Log").Range("r2").Offset(a, 0) = Comments1

Sheets("Log").Range("s2").Offset(a, 0) = Unit3
Sheets("Log").Range("t2").Offset(a, 0) = Fat3
Sheets("Log").Range("u2").Offset(a, 0) = Protein3
Sheets("Log").Range("v2").Offset(a, 0) = CurdCombo1
Sheets("Log").Range("w2").Offset(a, 0) = pH1
Sheets("Log").Range("x2").Offset(a, 0) = Mixability1
Sheets("Log").Range("y2").Offset(a, 0) = Comments1

Sheets("Log").Range("z2").Offset(a, 0) = Unit4
Sheets("Log").Range("aa2").Offset(a, 0) = Fat4
Sheets("Log").Range("ab2").Offset(a, 0) = Protein4
Sheets("Log").Range("ac2").Offset(a, 0) = CurdCombo4
Sheets("Log").Range("ad2").Offset(a, 0) = pH4
Sheets("Log").Range("ae2").Offset(a, 0) = Mixability4
Sheets("Log").Range("af2").Offset(a, 0) = Comments4
Sheets("Log").Range("ag2").Offset(a, 0) = Unit5
Sheets("Log").Range("ah2").Offset(a, 0) = Fat5
Sheets("Log").Range("ai2").Offset(a, 0) = Protein5
Sheets("Log").Range("aj2").Offset(a, 0) = CurdCombo4
Sheets("Log").Range("ak2").Offset(a, 0) = pH4
Sheets("Log").Range("al2").Offset(a, 0) = Mixability4
Sheets("Log").Range("am2").Offset(a, 0) = Comment4

Sheets("Log").Range("an2").Offset(a, 0) = Unit6
Sheets("Log").Range("ao2").Offset(a, 0) = Fat6
Sheets("Log").Range("ap2").Offset(a, 0) = Protein6
Sheets("Log").Range("aq2").Offset(a, 0) = CurdCombo4
Sheets("Log").Range("ar2").Offset(a, 0) = pH4
Sheets("Log").Range("as2").Offset(a, 0) = Mixability4
Sheets("Log").Range("at2").Offset(a, 0) = Comments4

Sheets("Log").Range("au2").Offset(a, 0) = Unit7
Sheets("Log").Range("av2").Offset(a, 0) = Fat7
Sheets("Log").Range("aw2").Offset(a, 0) = Protein7
Sheets("Log").Range("ax2").Offset(a, 0) = CurdCombo7
Sheets("Log").Range("ay2").Offset(a, 0) = pH7
Sheets("Log").Range("az2").Offset(a, 0) = Mixability7
Sheets("Log").Range("ba2").Offset(a, 0) = Comments7

Sheets("Log").Range("Bb2").Offset(a, 0) = Unit8
Sheets("Log").Range("Bc2").Offset(a, 0) = Fat8
Sheets("Log").Range("Bd2").Offset(a, 0) = Protein8
Sheets("Log").Range("Be2").Offset(a, 0) = CurdCombo7
Sheets("Log").Range("Bf2").Offset(a, 0) = pH7
Sheets("Log").Range("Bg2").Offset(a, 0) = Mixability7
Sheets("Log").Range("Bh2").Offset(a, 0) = Comments7

Sheets("Log").Range("Bi2").Offset(a, 0) = Unit9
Sheets("Log").Range("Bj2").Offset(a, 0) = Fat9
Sheets("Log").Range("Bk2").Offset(a, 0) = Protein9
Sheets("Log").Range("Bl2").Offset(a, 0) = CurdCombo7
Sheets("Log").Range("Bm2").Offset(a, 0) = pH7
Sheets("Log").Range("Bn2").Offset(a, 0) = Mixability7
Sheets("Log").Range("Bo2").Offset(a, 0) = Comments7

Sheets("Log").Range("Bp2").Offset(a, 0) = Unit10
Sheets("Log").Range("Bq2").Offset(a, 0) = Fat10
Sheets("Log").Range("Br2").Offset(a, 0) = Protein10
Sheets("Log").Range("Bs2").Offset(a, 0) = CurdCombo10
Sheets("Log").Range("Bt2").Offset(a, 0) = pH10
Sheets("Log").Range("Bu2").Offset(a, 0) = Mixability10
Sheets("Log").Range("Bv2").Offset(a, 0) = Comments10

Sheets("Log").Range("Bw2").Offset(a, 0) = Unit11
Sheets("Log").Range("Bx2").Offset(a, 0) = Fat11
Sheets("Log").Range("By2").Offset(a, 0) = Protein11
Sheets("Log").Range("Bz2").Offset(a, 0) = CurdCombo10
Sheets("Log").Range("ca2").Offset(a, 0) = pH10
Sheets("Log").Range("Cb2").Offset(a, 0) = Mixability10
Sheets("Log").Range("Cc2").Offset(a, 0) = Comments10

Sheets("Log").Range("Cd2").Offset(a, 0) = Unit12
Sheets("Log").Range("Ce2").Offset(a, 0) = Fat12
Sheets("Log").Range("Cf2").Offset(a, 0) = Protein12
Sheets("Log").Range("Cg2").Offset(a, 0) = CurdCombo10
Sheets("Log").Range("Ch2").Offset(a, 0) = pH10
Sheets("Log").Range("Ci2").Offset(a, 0) = Mixability10
Sheets("Log").Range("Cj2").Offset(a, 0) = Comments10

Sheets("Log").Range("Ck2").Offset(a, 0) = Unit13
Sheets("Log").Range("Cl2").Offset(a, 0) = Fat13
Sheets("Log").Range("Cm2").Offset(a, 0) = Protein13
Sheets("Log").Range("Cn2").Offset(a, 0) = CurdCombo13
Sheets("Log").Range("Co2").Offset(a, 0) = pH13
Sheets("Log").Range("Cp2").Offset(a, 0) = Mixability13
Sheets("Log").Range("Cq2").Offset(a, 0) = Comments13

Sheets("Log").Range("Cr2").Offset(a, 0) = Unit14
Sheets("Log").Range("Cs2").Offset(a, 0) = Fat14
Sheets("Log").Range("Ct2").Offset(a, 0) = Protein14
Sheets("Log").Range("Cu2").Offset(a, 0) = CurdCombo13
Sheets("Log").Range("Cv2").Offset(a, 0) = pH13
Sheets("Log").Range("Cw2").Offset(a, 0) = Mixability13
Sheets("Log").Range("Cx2").Offset(a, 0) = Comments13

Sheets("Log").Range("Cy2").Offset(a, 0) = Unit15
Sheets("Log").Range("Cz2").Offset(a, 0) = Fat15
Sheets("Log").Range("da2").Offset(a, 0) = Protein15
Sheets("Log").Range("Db2").Offset(a, 0) = CurdCombo13
Sheets("Log").Range("Dc2").Offset(a, 0) = pH13
Sheets("Log").Range("Dd2").Offset(a, 0) = Mixability13
Sheets("Log").Range("De2").Offset(a, 0) = Comments13

Sheets("Log").Range("Df2").Offset(a, 0) = Unit16
Sheets("Log").Range("Dg2").Offset(a, 0) = Fat16
Sheets("Log").Range("Dh2").Offset(a, 0) = Protein16
Sheets("Log").Range("Di2").Offset(a, 0) = CurdCombo16
Sheets("Log").Range("Dj2").Offset(a, 0) = pH16
Sheets("Log").Range("Dk2").Offset(a, 0) = Mixability16
Sheets("Log").Range("Dl2").Offset(a, 0) = Comments16

Sheets("Log").Range("Dm2").Offset(a, 0) = Unit17
Sheets("Log").Range("Dn2").Offset(a, 0) = Fat17
Sheets("Log").Range("Do2").Offset(a, 0) = Protein17
Sheets("Log").Range("Dp2").Offset(a, 0) = CurdCombo16
Sheets("Log").Range("Dq2").Offset(a, 0) = pH16
Sheets("Log").Range("Dr2").Offset(a, 0) = Mixability16
Sheets("Log").Range("Ds2").Offset(a, 0) = Comments16

Sheets("Log").Range("Dt2").Offset(a, 0) = Unit18
Sheets("Log").Range("Du2").Offset(a, 0) = Fat18
Sheets("Log").Range("Dv2").Offset(a, 0) = Protein18
Sheets("Log").Range("Dw2").Offset(a, 0) = CurdCombo16
Sheets("Log").Range("Dx2").Offset(a, 0) = pH16
Sheets("Log").Range("Dy2").Offset(a, 0) = Mixability16
Sheets("Log").Range("Dz2").Offset(a, 0) = Comments16

Sheets("Log").Range("ea2").Offset(a, 0) = Unit19
Sheets("Log").Range("Eb2").Offset(a, 0) = Fat19
Sheets("Log").Range("Ec2").Offset(a, 0) = Protein19
Sheets("Log").Range("Ed2").Offset(a, 0) = CurdCombo19
Sheets("Log").Range("Ee2").Offset(a, 0) = pH19
Sheets("Log").Range("Ef2").Offset(a, 0) = Mixability19
Sheets("Log").Range("Eg2").Offset(a, 0) = Comments19

Sheets("Log").Range("Eh2").Offset(a, 0) = Unit20
Sheets("Log").Range("Ei2").Offset(a, 0) = Fat20
Sheets("Log").Range("Ej2").Offset(a, 0) = Protein20
Sheets("Log").Range("Ek2").Offset(a, 0) = CurdCombo19
Sheets("Log").Range("El2").Offset(a, 0) = pH19
Sheets("Log").Range("Em2").Offset(a, 0) = Mixability19
Sheets("Log").Range("En2").Offset(a, 0) = Comments19

Sheets("Log").Range("Eo2").Offset(a, 0) = Unit21
Sheets("Log").Range("Ep2").Offset(a, 0) = Fat21
Sheets("Log").Range("Eq2").Offset(a, 0) = Protein21
Sheets("Log").Range("Er2").Offset(a, 0) = CurdCombo19
Sheets("Log").Range("Es2").Offset(a, 0) = pH19
Sheets("Log").Range("Et2").Offset(a, 0) = Mixability19
Sheets("Log").Range("Eu2").Offset(a, 0) = Comments19

Sheets("Log").Range("Ev2").Offset(a, 0) = CompFatResult
Sheets("Log").Range("Ew2").Offset(a, 0) = CompProtResult
Sheets("Log").Range("Ex2").Offset(a, 0) = AverageFat
Sheets("Log").Range("Ey2").Offset(a, 0) = AverageProtein
Sheets("log").Range("EZ2").Offset(a, 0) = LabNumber

Sheets("Log").Range("FA2").Offset(a, 0) = FatRef
Sheets("Log").Range("FB2").Offset(a, 0) = ProtRef
Sheets("Log").Range("FC2").Offset(a, 0) = FatNIR
Sheets("Log").Range("FD2").Offset(a, 0) = ProtNIR
Sheets("Log").Range("FF2").Offset(a, 0) = BagLoss



' Next xCell


Sheets("Front Sheet").Unprotect


' Sheets("Front Sheet").Range("b1") = ProductCombo
Sheets("Front Sheet").Range("D1") = Cypher
' Sheets("Front Sheet").Range("C32") = StaffName
' Sheets("Front Sheet").Range("E33") = DTPicker1


Sheets("Front Sheet").Protect



ActiveWorkbook.Save

Dim Msg2, Style2, Title2, Help2, Ctxt2, Response2, MyString2
Msg2 = "Entered Successfully - Do you want the Front Page to be Printed?"
Style2 = vbYesNo + vbDefaultButton2
Ctxt2 = 1000
Response = MsgBox(Msg2, Style2, Title2, Help2, Ctxt2)
If Response = vbYes Then
GoTo 15
Else
GoTo 22
End If

15
' Print the page
'
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"



'888
'Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long
' Set ExcelLastCell = Sheets("Log").Cells.SpecialCells(xlLastCell)
' lLastDataRow = ExcelLastCell.Row
' lRow = ExcelLastCell.Row

' Do While Application.CountA(Sheets("Log").Rows(lRow)) = 0 And lRow <> 1
' lRow = lRow - 1
' Loop
' lLastDataRow = lRow
' GetLastRowWithData = lLastDataRow

' Dim ExcelLastCell2 As Object, lCol As Long, lLastDataCol As Long, col As Long
' Set ExcelLastCell2 = Sheets("Log").Cells.SpecialCells(xlLastCell)
' lLastDataCol = ExcelLastCell.End(xlUp).Column
' lCol = ExcelLastCell2.Column

' Do While Application.CountA(Sheets("Log").Range("A" & lLastDataRow).Columns(lCol)) = 0 And lCol <> 1
' lCol = lCol - 1
' Loop
' lLastDataCol = lCol




'GetLastColumnWithData = lLastDataCol

' l = Sheets("log").Range("aindex") - 1


' p = lLastDataCol - 1

' Sheets("Log").Range("I2").Offset(l, p) = StaffName


ActiveWorkbook.Save
MsgBox ("Front Page Printed - Workbook Saved")

Unload TopSheetInfo
GoTo 25
22

MsgBox ("Workbook Saved")
Unload TopSheetInfo
GoTo 25
20
MsgBox ("Please Make Your Alterations")

25


End Sub

 

[Tinbendr]

Please use code tags when posting code. Only post the relevent code in question.

Excel does a pretty good job of converting text to the formatted cell.

But if you're not having success, in the VBE, search for Type Conversion Functions.

These functions attempts to coherse a expression into the required type.

 

[miricleman]

Thanks for the advice Tinbendr. The code I want to be in number format when copied to the spread sheet is all the code in this range and subsequent info.

Sheets("Log").Range("e2").Offset(a, 0) = Unit1
    Sheets("Log").Range("f2").Offset(a, 0) = Fat1
    Sheets("Log").Range("g2").Offset(a, 0) = Protein1
    Sheets("Log").Range("h2").Offset(a, 0) = CurdCombo1
    Sheets("Log").Range("i2").Offset(a, 0) = pH1
    Sheets("Log").Range("j2").Offset(a, 0) = Mixability1

I was going to upload the spread sheet but could not work how to do that! How does excel covert information from VBA to numbers automatically when the information from VBA is posted? Also went to Visual Basic Editor (VBE?) to try to find the type conversion functions you mentioned however I am only a novice and have taught myself from information, from sites like this, so could not find this information you kindly provided. Any more guidance would be greatly welcomed.

 

[Tinbendr]

In the search bar at the top left in the VBE, Enter Type Conversion Functions

It should look something like this.

Type Conversion Functions

<tbody>
</tbody>

Each function coerces an expression to a
specific data type.
Syntax
CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CLngLng(expression) (Valid on
64-bit platforms only.)
CLngPtr(expression)
CSng(expression)
CStr(expression)
CVar(expression)
The required expression argument is any
string expression or numeric expression.
Return Types
The function name determines the return type as shown in the following:
....

I was going to upload the spread sheet

It's not allowed here, but you can use one on the filesharing services out there and then post a link. I use Box.net

How does excel covert information

Excel tries to follow the format of the cell. If you have it set to General, Excel doesn't do too much with it, but if it's a date or number, it tries to understand what you mean when you paste text to it. Sometimes it works. Sometimes it doesn't. Then you have to be specific in putting the right data type in the cell. That's where the data conversion functions come in.