Changing the active cell

Question

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

Have you looked at any potential problems with SaSh when you pass it into the PriceSearch Function? — Paradox, Jun 15 '15 at 20:02
A function called from a worksheet cell is restricted in what it can do: https://support.microsoft.com/en-us/kb/170787 You can rewrite your `PriceSearch` sub to avoid using `ActiveCell` and `Select` — Tim Williams, Jun 15 '15 at 20:02
Try to be more specific. i.e. YourWorkbook.YourWorksheet.Range("A1").Select — Paradox, Jun 15 '15 at 20:04
`Select` and `ActiveSheet/Cell` are going to cause headaches. Avoid! https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — citizenkong, Jun 16 '15 at 09:35

score 0 · Accepted Answer · answered Jun 15 '15 at 21:39

The comments made by @Paradox and @Tim are all valid.

To specifically answer your question, you cannot change the ActiveCell from code but instead use the Range or Cells to set a reference to the range:

Public Sub PriceSearch(SaSh As Worksheet)

    Dim StartNumber As Integer
    Dim EndNumber As Integer
    Dim CellToCheck As Range

    EndNumber = 1000

    'Search the Query Sheet and starting search at the top left corner of the sheet
    'Keep searching the A column until you come across the Canadian Currency post
     For StartNumber = 1 To EndNumber

        Set CellToCheck = SaSh.Cells(RowIndex:=StartNumber, ColumnIndex:=1)

        'Checking to see if the current cell is Canadian Currency
        If WorksheetFunction.IsNumber(CurrencyValuation(CellToCheck.Value)) Then
            NewValue = CurrencyValuation(CellToCheck.Value)
            Exit For
        End If
    Next StartNumber

End Sub

Thank you for your help. – user3429358 Jun 19 '15 at 17:51 — user3429358, Jun 19 '15 at 17:51

Changing the active cell

1 Answers1