VBA to read cell contents, not formula

NPigna

New Member
Joined
Mar 5, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
L

Legacy 456155

Guest
.Replacement.Text = pricingSheet.Sheets(1).Cells(macroWorkbook.Sheets(1).Cells(i, 2), macroWorkbook.Sheets(1).Cells(i, 3)).Text
.Replacement.Text = pricingSheet.Sheets(2).Cells(macroWorkbook.Sheets(1).Cells(i, 5), macroWorkbook.Sheets(1).Cells(i, 6)).Text

Range.Text property

:)
 
Solution

NPigna

New Member
Joined
Mar 5, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
.Replacement.Text = pricingSheet.Sheets(1).Cells(macroWorkbook.Sheets(1).Cells(i, 2), macroWorkbook.Sheets(1).Cells(i, 3)).Text
.Replacement.Text = pricingSheet.Sheets(2).Cells(macroWorkbook.Sheets(1).Cells(i, 5), macroWorkbook.Sheets(1).Cells(i, 6)).Text

Range.Text property

:)
OH MY GOODNESS!! IT WORKS! Thank you soo much and it turned out soo simple. Thank you!😊
 

NPigna

New Member
Joined
Mar 5, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi, I have a question. The code is awesome and does just what I want. Now I may share it with some of my co-workers. I want to make it a little more user friendly. When we made it It was the first code my friend ever did so noww we want to change one thing. Right now I have to identify the column by a number-so the letter a is , B is 2, C is 3 etc. How can I change the code so i can use the letter? A is A, B is a B etc
 

Watch MrExcel Video

Forum statistics

Threads
1,114,032
Messages
5,545,629
Members
410,696
Latest member
JTrehan
Top