I'm going crazy trying to get Cell.Offset working

[cappy2112]

Hi,

I'm somewhat new to VBA and I never use Excel for its intended purpose (I typically use it for inventory lists, etc).
(I'm not involved with plottind/visualization, as a rule of thumb).
I did work with VB6 for a few years, back in pre .NET days though. For my day-day work, I use Python and a proprietary
scripting language, where I work.

I'm running Office /Excel 2013 (I think) on Windows 7 Pro, 64-Bit.

However I've got a short term project to automate what is now being done manually.
Opening a logfile, copying and pasting the data into Excel, and generating plots.
Fortunately, the plots have already been created by someone knowledgable in Excel, I'm just populating the
rows & columns with data I've copied from the logfile, via an editor.

That said, I've made pretty good progress reading the data from the logfile into a VBA Collection,
now I'm trying to get that data into the appropriate rows & columns in a worksheet.

For reasons I've been struggling with for many days, when I'm able to make the cell.offset field compile/run,
the Cell object keeps populating the same starting Cell #, which is J8. This makes no sense to me.
At the moment, I'm doing something wrong and cannot even get the Cell.proprty field to work.
I keep running into this issue, but don't know why.

I've googled my brains out and tried many answers tht were returned from the searches.

My approach may not make sense to you, it's what I've started with.
I simply want to populate a column of data, J8-J24, then proceeed to the next column.

The range_ object appears to give me that functionality. When range_ is moved during the second
iteration of the for loop, it is indeed pointing to column K8.

For now, I will manully increment the variables TestNumberCount and BlkSizeCount, in order that the data I need
to populate the spreadsheet is correct. I'll figure out how to iterate over the nested collections objects later.

Darn- why cant I attach a screenshot of my code?

Sub PopulateWorksheet(PerformanceData As Collection, Sheetname As String, StartCell As String, EndCell As String, ColumnOffset As Integer)


Const TESTDESCRITEM As Integer = 1
Const DATAITEM As Integer = 2
Const IOPSITEM As Integer = 3
Const MBSITEM As Integer = 4
Dim BlockSize As String
Dim IOPs As String
Dim MBs As Variant
Dim BlkSizeCount As Integer
Dim TestNumberCount As Integer
Dim Cell As Range
Dim range_ As Range
Dim RowOffset As Integer
Dim PerfData As Object
Dim x As Integer


'Set range_ = Worksheets("My Try").Range("J8:J24")
Set range_ = Worksheets("My Try").Range("J8:Y24")
BlkSizeCount = 1
TestNumberCount = 1
RowOffset = 0

For Each Cell In range_ ' iterates over the columns in the range_ object
For BlkSizeCount = 1 To 17
MBs = PerformanceData.Item(TestNumberCount).Item(DATAITEM).Item(BlkSizeCount).Item(IOPSITEM)
Cell.Activate
Cell.NumberFormat = "General"
Cell.Value = CStr(MBs)
RowOffset = RowOffset + 1
Cell.Offset(rowoffset, 0) <- THIS IS THE PROBLEM!!!
Next BlkSizeCount

Next Cell

End Sub

 

[JLGWhiz]

You're probably getting a 1004 runtime error wondering what you want to do with the incomplete statement.

Cell.Offset(rowoffset, 0) = ?

The code without the = symbol is simply a cell designation like putting in Range("A1") and nothing else. VBA is expecting some code to tell it what is to be done with that range object. something like

cell.Offset(rowoffset, 0).Select
cell.Offset(rowoffset, 0) = "ABC"

 

[cappy2112]

You're probably getting a 1004 runtime error wondering what you want to do with the incomplete statement.

Cell.Offset(rowoffset, 0) = ?

The code without the = symbol is simply a cell designation like putting in Range("A1") and nothing else. VBA is expecting some code to tell it what is to be done with that range object. something like

cell.Offset(rowoffset, 0).Select
cell.Offset(rowoffset, 0) = "ABC"

The code that s giving me the error was highlighted by the IDE. I tried posting a screenshot but this website doesn't allow
that.
cell.Offset(rowoffset, 0) = MBS is what I want to do, but the IDE give an error msg about the offset property.
There was a time when I didn't get the error msg for Offset, but the data I am trying populate the worksheet with
gets written to the same cell, every iteration of the loop.
Every example of Offset i could find didn't work.

 

[JLGWhiz]

I am not sure what you are trying to do, but as currently written the code is going to the first cell in your designated range J8:Y24 and inserting a value of MBs as a string. It then iterates through the inner loop incrementing the RowOffset variable by 1 on each iteration. If your 'Cell.Offset(RowOffset, 0)' line did insert a value into a cell it would start at cell J9 and continue downward for each iteration of the inner loop. The Offset appears to be working as it is designed to work. I suggest that you step through your code by opening the vb editor (Alt + F11), click anywhere inside the macro once and then use F8 function key to step through the procedure. Since the procedure has values passed to it from another source, you would probably have to start in that source procedure so that the passed values will be initialized in this procedure. This method allows you to see what the variable values are as each line of code executes and you can determine where changes need to be made in your code to get the correct values.

I would also suggest that you not use Excel keywords like 'cell' and 'range' as variables because they can cause undesired rusults in some cases. You can use similar words like cel and rng for the variables to help you know what the variable represents.

 

[cappy2112]

I suggest that you step through your code by opening the vb editor .

I don't need to step through it, sine I know the code isn't working the what I want.
I 've already posted that my code was writing all of the data to one cell (without the call to .Offset, since that wasn't working)
This is the whole problem- in that I couldn't get the .Offset method working. Until now, that is.
I didn't realize that .Offset() could be used to insert data into a cell.
Now, I've removed the assignment to Cell.Value, and replaced it with the call to Cell.Offset()

This is the working code I have now.

Public Sub PopulateWorksheet(PerformanceData As Collection, Sheetname As String, StartCell As String, EndCell As String, ColumnOffset As Integer)


Const TESTDESCRITEM As Integer = 1
Const DATAITEM As Integer = 2
Const IOPSITEM As Integer = 3
Const MBSITEM As Integer = 4
Dim BlockSize As String
Dim IOPs As String
Dim MBs As Variant
Dim BlkSizeCount As Integer
Dim TestNumberCount As Integer
Dim Cell As Range
Dim range_ As Range
Dim RowOffset As Integer
Dim PerfData As Object
Dim x As Integer


'Set range_ = Worksheets("My Try").Range("J8:J24")
Set range_ = Worksheets("My Try").Range("J8:Y24")
BlkSizeCount = 1
TestNumberCount = 1


For Each Cell In range_ ' iterates over the columns in the range_ object
RowOffset = 0
For BlkSizeCount = 1 To 17
MBs = PerformanceData.Item(TestNumberCount).Item(DATAITEM).Item(BlkSizeCount).Item(IOPSITEM)
Cell.NumberFormat = "General"
'Cell.Value = CStr(MBs) ' Not needed, since the Cell.Offset() line handles the assignment
Cell.Offset(RowOffset, 0) = CStr(MBs)
RowOffset = RowOffset + 1


Next BlkSizeCount

TestNumberCount = TestNumberCount + 1

Next Cell

End Sub

Thank you very much for your help.
Replacing the assignment in Cell.Value with Cell.Offset() = MBS, did the trick!
I wish I would have posted a message last week.

 

[JLGWhiz]

I don't need to step through it, sine I know the code isn't working the what I want.
I 've already posted that my code was writing all of the data to one cell (without the call to .Offset, since that wasn't working)
This is the whole problem- in that I couldn't get the .Offset method working. Until now, that is.
I didn't realize that .Offset() could be used to insert data into a cell.
Now, I've removed the assignment to Cell.Value, and replaced it with the call to Cell.Offset()

This is the working code I have now.




Thank you very much for your help.
Replacing the assignment in Cell.Value with Cell.Offset() = MBS, did the trick!
I wish I would have posted a message last week.

Although I do not follow your explanation or logic, if you are happy, then I am happy.
regards, JLG

 

[cappy2112]

Replacing the assignment in Cell.Value with Cell.Offset() = MBS, did the trick!
I wish I would have posted a message last week.

When I was using Cell.Value = MBS, without the call to Cell.Offset(),
every value of MBS was being written to the same cell, even though Cell.Address was changing with each loop iteration.
I can't explain this.
From the many searches I did, It appeared that Cell.Offset(row,col) was the solution,
but I didn't know that I needed to do the assignment inthe same expression.
It thought that calling Cell.Offset() with the new row & col offset values was suffificient.
Obviously, I was wrong.

I hope this is clear.