Hi! Can someone help me? I do not really understand coding but am learning a little. Please excuse my mis-use of terms. A friend, who is new to coding, wrote a code for me in macro VBA. We got it to do what we want except for one thing. It takes the code, opens a word document and inputs the cells from excel into the word document.. Some of the cells we need ,though, it puts the actual number from the formula -like up to 14 or so after the decimal(34.456667). How do we add a code to have it just enter the number in the cell that we see?( we can't use rounded up because some we do need the numbers after the decimal if they are visible. Also maybe even a comma for numbers in the thousands? Thank you in advance for any help you can give me! It looks messy because I have tried different things and left them in after an apostrophe. Unfortunately I don't understand enough to use ideas without precise instructions. Here is the code
Sub FillProposal()
'Define variables
Dim pricingSheet As Workbook
Dim macroWorkbook As Workbook
Dim wdApp As Word.Application
Dim proposalTemplate As Word.Document
Dim listLength As Integer
Dim strText As String
strText = Range("A1").Text
'Dim dblNum As Double
dblNum = Range("A1").Value2
'Where you have reference to workbook cells in the code, surround the code with the Format function, like:
'whatever = Format(workbookcellthing, "#,##0.0")
'Identify workbooks and open templates
Set pricingSheet = ActiveWorkbook
Windows("macro.xlsm").Activate
Set macroWorkbook = ActiveWorkbook
Set wdApp = New Word.Application
'Change file path when template is moved or renamed
Set proposalTemplate = wdApp.Documents.Open("C:\Users\natpi\Documents\CODE TEMPLATES\PROPOSAL CODED TEMPLATE REV 10-10-2020.docx")
'C:\Users\natpi\Downloads\PROPOSAL CODED TEMPLATE REV 4-2-2020.docx")
'C:\Users\Fine Arts\Documents\PRECISION SAFE SIDEWALKS\PSS TEMPLATES\PROPOSAL TEMPLATE REV 4-9-19 with excel pts (1).docx")
wdApp.Visible = True
'Range("A1:B3").Copy
'Range("C1").PasteSpecial Paste:=xlPasteValues this one might work
'Find and replace Summary table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(1))
For i = 3 To listLength
wdApp.Activate
With wdApp.Selection.Find
.Text = macroWorkbook.Sheets(1).Cells(i, 1)
.Replacement.Text = pricingSheet.Sheets(1).Cells(macroWorkbook.Sheets(1).Cells(i, 2), macroWorkbook.Sheets(1).Cells(i, 3))
.Execute Replace:=wdReplaceAll '.Execute Replace:=wdReplaceSpecial = wdText this still ran but didnt fill in data
End With
'Range("C1").Value = 0.49999999
'Range("C1").NumberFormat = "0.00"
'Debug.Print "The Value property is: " & Range("C1").Value
'Debug.Print "The Text property is: " & Range("C1").Text
Next
'Find and replace Green table
'Range("C1").Value = 0.49999999
'Range("C1").NumberFormat = "0.00"
'Debug.Print "The Value property is: " & Range("C1").Value
'Debug.Print "The Text property is: " & Range("C1").Text
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(4))
For i = 3 To listLength
wdApp.Activate
With wdApp.Selection.Find
.Text = macroWorkbook.Sheets(1).Cells(i, 4)
'Range("C1").Value = 0.49999999
'Range("C1").NumberFormat = "0.00"
'Debug.Print "The Value property is: " & Range("C1").Value
'Debug.Print "The Text property is: " & Range("C1").Text
.Replacement.Text = pricingSheet.Sheets(2).Cells(macroWorkbook.Sheets(1).Cells(i, 5), macroWorkbook.Sheets(1).Cells(i, 6))
.Execute Replace:=wdReplaceAll
End With
Next
End Sub
Sub FillProposal()
'Define variables
Dim pricingSheet As Workbook
Dim macroWorkbook As Workbook
Dim wdApp As Word.Application
Dim proposalTemplate As Word.Document
Dim listLength As Integer
Dim strText As String
strText = Range("A1").Text
'Dim dblNum As Double
dblNum = Range("A1").Value2
'Where you have reference to workbook cells in the code, surround the code with the Format function, like:
'whatever = Format(workbookcellthing, "#,##0.0")
'Identify workbooks and open templates
Set pricingSheet = ActiveWorkbook
Windows("macro.xlsm").Activate
Set macroWorkbook = ActiveWorkbook
Set wdApp = New Word.Application
'Change file path when template is moved or renamed
Set proposalTemplate = wdApp.Documents.Open("C:\Users\natpi\Documents\CODE TEMPLATES\PROPOSAL CODED TEMPLATE REV 10-10-2020.docx")
'C:\Users\natpi\Downloads\PROPOSAL CODED TEMPLATE REV 4-2-2020.docx")
'C:\Users\Fine Arts\Documents\PRECISION SAFE SIDEWALKS\PSS TEMPLATES\PROPOSAL TEMPLATE REV 4-9-19 with excel pts (1).docx")
wdApp.Visible = True
'Range("A1:B3").Copy
'Range("C1").PasteSpecial Paste:=xlPasteValues this one might work
'Find and replace Summary table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(1))
For i = 3 To listLength
wdApp.Activate
With wdApp.Selection.Find
.Text = macroWorkbook.Sheets(1).Cells(i, 1)
.Replacement.Text = pricingSheet.Sheets(1).Cells(macroWorkbook.Sheets(1).Cells(i, 2), macroWorkbook.Sheets(1).Cells(i, 3))
.Execute Replace:=wdReplaceAll '.Execute Replace:=wdReplaceSpecial = wdText this still ran but didnt fill in data
End With
'Range("C1").Value = 0.49999999
'Range("C1").NumberFormat = "0.00"
'Debug.Print "The Value property is: " & Range("C1").Value
'Debug.Print "The Text property is: " & Range("C1").Text
Next
'Find and replace Green table
'Range("C1").Value = 0.49999999
'Range("C1").NumberFormat = "0.00"
'Debug.Print "The Value property is: " & Range("C1").Value
'Debug.Print "The Text property is: " & Range("C1").Text
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(4))
For i = 3 To listLength
wdApp.Activate
With wdApp.Selection.Find
.Text = macroWorkbook.Sheets(1).Cells(i, 4)
'Range("C1").Value = 0.49999999
'Range("C1").NumberFormat = "0.00"
'Debug.Print "The Value property is: " & Range("C1").Value
'Debug.Print "The Text property is: " & Range("C1").Text
.Replacement.Text = pricingSheet.Sheets(2).Cells(macroWorkbook.Sheets(1).Cells(i, 5), macroWorkbook.Sheets(1).Cells(i, 6))
.Execute Replace:=wdReplaceAll
End With
Next
End Sub