I am using the function located here:
https://www.extendoffice.com/documents/excel/1497-excel-convert-decimal-degrees-to-degrees-minutes-seconds.html
in order to convert the degree min sec values to the decimal ones.
Unfortunately, I've encountered the problem.
I found that is not so simple as shown in this example:
Fill formula down till last row in column
in the other hand, the example more dedicated for my situation
Finding the Last Row and using it in a formula
also wans't successful.
I would like to know why I am getting:
- the result to the second row only
- the "@" character before by formula (as I can see in the bar).
What is wrong with my code then?
Sub Sun()
Dim rng As Range, cell As Range
Dim wors As Worksheet
Set wors = ThisWorkbook.ActiveSheet
Dim lastRow As Long, LastRow2 As Long
lastRow = wors.Range("P" & wors.Rows.Count).End(xlUp).Row
LastRow2 = wors.Range("Q" & wors.Rows.Count).End(xlUp).Row
Set rng = wors.Range("P1:P" & lastRow)
wors.Columns("E").Copy
wors.Columns("P").PasteSpecial xlPasteValues
For Each cell In rng
cell = WorksheetFunction.Substitute(cell, " ", "° ", 1)
Next
Call Degree
Range("Q2:Q" & lastRow).Formula = "=ConvertDecimal (P" & LastRow2 & " )"
End Sub
Moreover, it comes to an error:
Invalid procedure call or argument
, with debugging:
xDeg = Val(Left(pInput, InStr(1, pInput, "°") - 1))
the code from the function:
Function ConvertDecimal(pInput As String) As Double
'Updateby20140227
Dim xDeg As Double
Dim xMin As Double
Dim xSec As Double
xDeg = Val(Left(pInput, InStr(1, pInput, "°") - 1))
xMin = Val(Mid(pInput, InStr(1, pInput, "°") + 2, _
InStr(1, pInput, "'") - InStr(1, pInput, _
"°") - 2)) / 60
xSec = Val(Mid(pInput, InStr(1, pInput, "'") + _
2, Len(pInput) - InStr(1, pInput, "'") - 2)) _
/ 3600
ConvertDecimal = xDeg + xMin + xSec
End Function
How can I drag this formula down to the last row?
question from:
https://stackoverflow.com/questions/66066924/vba-excel-filling-formula-with-external-function-down-till-the-last-row