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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
.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

:)
 
Upvote 0
Solution
.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!?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top