0

very new to VBA and not great at any type of coding. Syntax is definitely not my strong suit, and I think that is all this error is. I am writing a program to cycle through data imported from a CSV. Each column is a sensor readout, and each row is a single timestep. I am working with this data in some variable number of "steps" that represent discrete substages of the process the data was recording. This is handled by the FirstRow and LastRow variables, and the column of interest in this case is represented by ColOfInt. Essentially, I am trying to get the Mode of the values in column ColOfInt using the WorksheetFunction.Mode function. All of this is within a loop that advances steps, and I have to call between different worksheets to pull and write values. That is why I am using Range(Cells,Cells) -- I need to be able to loop through rows and columns, so "A1" reference doesn't really work well for this.

When I write the code like this, it does do what I want, but I don't like it having to actually change worksheets using the Sheets.Activate:

Sheets("CSV Import").Activate
myRange = Range(Cells(FirstRow, ColOfInt), Cells(LastRow, ColOfInt))
TargetDens = WorksheetFunction.Mode(myRange)
Sheets("Operations").Cells(StepNumber, 27).Value = TargetDens 'this line just writes the value to the output spreadsheet

However, when I write it like this I get Runtime error 1004, Application or Object Defined Error and the first row turns yellow.

myRange = Sheets("CSV Import").Range(Cells(FirstRow, ColOfInt), Cells(LastRow, ColOfInt))
TargetDens = WorksheetFunction.Mode(myRange)
Sheets("Operations").Cells(StepNumber, 27).Value = TargetDens

I really don't understand VBA's syntax, but what is wrong with Sheets(xyz).Range(ABC)? I have been using Sheets(xyz).Cells(abc) all over this program and it works fine and can pull and write across different sheets, but doesn't seem to work with Range, which I believe I have to use to use the Mode function. Sorry the question is so long, just trying to be clear. Thank you for any help.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
pushrods
  • 1
  • `With Sheets("CSV Import")`, `myRange = .Range(.Cells(FirstRow, ColOfInt), .Cells(LastRow, ColOfInt))`, `End With`. Btw, it's misleading to name a variable `myRange` if you're not actually using it as a `Range`. If you were, then you'd need `Set`: `Set myRange = ...` – BigBen Apr 17 '23 at 15:42
  • BigBen, thank you that worked. I didn't know about `With`. Just trying to learn here, would you mind explaining what about `myRange` is not a `Range`? I thought a range was just when you were specifying multiple cells instead of a single cell. – pushrods Apr 17 '23 at 15:53
  • A `Range` is an object. You need `Set` for objects. Another good practice is to declare your variables, so `Dim myRange As Range`, then add the `Set` before `myRange = ...`. See [this](https://stackoverflow.com/questions/3872339/what-is-the-difference-between-dim-and-set-in-vba). – BigBen Apr 17 '23 at 15:54
  • So the more correct syntax in this case would be `Set myRange = .Range(.Cells(FirstRow, ColofInt), .Cells(LastRow, ColOfInt))` ? – pushrods Apr 17 '23 at 15:57
  • That's exactly right. – BigBen Apr 17 '23 at 15:58

0 Answers0