I am working on a project which basically transfers data from Access tables to Excel worksheets. Some of the data is in the form of formulas. Hence, a table would have a formula field with the formula string/text (eg. =utilities*3). Below is a portion of my code where I am getting the annoying problem.
You see, when the Excel worksheet opens, everything is transferred perfectly, except for the formulas. The formulas appear just as they are typed. If I place the cursor in the cell and hit the ENTER key, only then does the formula calculate. Which indicates that nothing is wrong with the formula.
I have tried several solutions from the other forums:
1) Recalculate the worksheet - I have tried this manually (by pressing F9) as well as implementing code. Neither worked.
2) I've checked that the number format in the cells is set to 'General' as opposed to 'Text'.
3) I have tried hard-codig the actual formula instead of referencing the record field, such that the code would look like like the example below, and this worked. But this doesn't solve my problem.
.Cells(CurrRow, 2).Formula = "Utilities*2"
4) I've tried setting the record field to a string and then assigning the cell formula to the string. This also didn't work.
I am stumped and confused. I know there's someone out there who's an expert who knows how to fix this. Any help will be appreciated...my job depends on it.
Do Until MySet.EOF
With MainWkSh
.Rows(CurrRow).Insert
.Cells(CurrRow, 1).Value = MySet!ProcessDataName
.Cells(CurrRow, 1).Name = MySet!ProcessDataName
.Cells(CurrRow, 1).Font.Italic = True
.Cells(CurrRow, 1).Font.Bold = False
.Cells(CurrRow, 2).Formula = MySet!Formula
End With
'-- Move to next record
MySet.MoveNext
'-- Increment Row
CurrRow = CurrRow + 1
Loop
You see, when the Excel worksheet opens, everything is transferred perfectly, except for the formulas. The formulas appear just as they are typed. If I place the cursor in the cell and hit the ENTER key, only then does the formula calculate. Which indicates that nothing is wrong with the formula.
I have tried several solutions from the other forums:
1) Recalculate the worksheet - I have tried this manually (by pressing F9) as well as implementing code. Neither worked.
2) I've checked that the number format in the cells is set to 'General' as opposed to 'Text'.
3) I have tried hard-codig the actual formula instead of referencing the record field, such that the code would look like like the example below, and this worked. But this doesn't solve my problem.
.Cells(CurrRow, 2).Formula = "Utilities*2"
4) I've tried setting the record field to a string and then assigning the cell formula to the string. This also didn't work.
I am stumped and confused. I know there's someone out there who's an expert who knows how to fix this. Any help will be appreciated...my job depends on it.
Do Until MySet.EOF
With MainWkSh
.Rows(CurrRow).Insert
.Cells(CurrRow, 1).Value = MySet!ProcessDataName
.Cells(CurrRow, 1).Name = MySet!ProcessDataName
.Cells(CurrRow, 1).Font.Italic = True
.Cells(CurrRow, 1).Font.Bold = False
.Cells(CurrRow, 2).Formula = MySet!Formula
End With
'-- Move to next record
MySet.MoveNext
'-- Increment Row
CurrRow = CurrRow + 1
Loop
