I was looking to create a program that examined a column of text in excel and extracted the first line that contained currency. The currency was in Canadian dollars and is formatted in "C $##.##" with no known upper bound but unlikely to reach 10,000 dollars. I was hoping to do this operation 500 times, and save the results in a Master sheet.
I am new to VBA in excel and would appreciate any help on the following code. The problem I am running into is an inability to change active sheet. The script returns #Value! and doesn't get past the line 'SaSh.Range("A1").Select'.
Option Explicit Public NewValue As Integer 'Amount of Money current item is being sold for Function PriceOfGoods(SaleString As String) Dim SaleSheet As Worksheet Set SaleSheet = Worksheets(SaleString) NewValue = -1 Call PriceSearch(SaleSheet) PriceOfGoods = NewValue End Function Public Sub PriceSearch(SaSh As Worksheet) Dim StartNumber As Integer Dim EndNumber As Integer Dim CurrentCell As String EndNumber = 1000 'Activating the Query Sheet and starting search at the top left corner of the sheet SaSh.Range("A1").Select 'Keep searching the A column until you come across the Canadian Currency post For StartNumber = 1 To EndNumber CurrentCell = ActiveCell.Value 'Checking to see if the current cell is Canadian Currency If WorksheetFunction.IsNumber(CurrencyValuation(CurrentCell)) Then NewValue = CurrencyValuation(ActiveCell.Value) Exit For End If 'Continue search in the next row ActiveCell.Offset(1, 0).Select Next StartNumber End Sub Function CurrencyValuation(CurrencyInput As String) Dim NewCurrency As Integer NewCurrency = WorksheetFunction.Substitute(CurrencyInput, "C", "") CurrencyValuation = NewCurrency End Function