-1

This is my first post and I'm relatively new to programming VBA. I have a simple subroutine as part of my larger code work. I am trying to find a string of text in a column, activate that cell so I can use values (integers) in that same row as the active cell (found text) (8 and 9 columns to the right), then use those new integers in a calculation. I'm receiving a Run-time error 424: Object Required in the row of code set rngNt. I do not understand what I'm missing. Any suggestions? it must be something simple. FYI my variables are all defined a the very top of my program.

This might be easier:

1) Find zoinks1 or zoinks 2 in column A row i. Zoinks1 and zoinks2 may appear multiple times. Find the first instance then...

2) Search downward in column A starting at column A row (i + 1) for donkey. Donkey may also appear multiple times, but I only want the next row encountered with donkey. Zoinks always precedes donkey, and another zoinks is always after a donkey (think rows A to C, D to G, H to M, etc).

3) Activate cell containing donkey (maybe unnecessary step?)

4) Use column H and I values from row containing donkey in calculation with column H in row containing zoinks with the resulting answer in zoinks row column M.

5) Repeat finding zoinks then donkey until reaching row 500.

' Define variable for use across all macros
Dim i As Integer
Dim j As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim rngNt As Range

Sub Countpassage()
Set ws1 = ThisWorkbook.Sheets("Data Figures & Other")
ws1.Activate

For i = 3 To ws1.Range("A500").End(xlUp).Row
    If ws1.Cells(i, 1).Value = "Zoinks1" Or _
    ws1.Cells(i, 1).Value = "Zoinks2" Then
    Set rngNt = ws1.Range(Cells(i + 1, 1), Cells(500, 1)).Find(What:="donkey", LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ws1.Cells(i, 13).Value = (Log((ActiveCell.Offset(0, 8) / ActiveCell.Offset(0, 9)) / ws1.Cells(i, 8)) / Log(2))
    End If
Next i
End Sub

EDIT: I've since deleted .Activate in both locations and Run-time 424 is no longer occurring. Now, i'm receiving Run-time error '13' Type Mismatch at this row of code:

ws1.Cells(i, 13).Value = (Log((ActiveCell.Offset(0, 8) / ActiveCell.Offset(0, 9)) / ws1.Cells(i, 8)) / Log(2))

I know the equation works with integers because I've tested it, but it doesn't seem to work with the ActiveCell.Offset inclusions. Once rngNt is set, I'm not sure how else to call the cells required for the calculation without using an offset command.

golfe
  • 1
  • 3

1 Answers1

0

You don't need to Activate your worksheet or anything really when using worksheet objects and ranges like your code does.

Activating is slow. Ideally avoid it at all costs.

I don't have VBA handy but I dont think .Activate returns anything (let alone a Range) so it wont assign anything to rngNg. If you really need to Activate it, do it as a separate command.

NickSlash
  • 4,758
  • 3
  • 21
  • 38