Trouble Looping Through an Excel Range using a 2D 'For Loop' with VBA

[Sarvazad]

Hello all,

Would someone please be able to assist me with this problem? Currently I am trying to loop through an arbitrary range in excel using VBA to fill any empty (null) cells with zeroes. I know how this could be done with an array in C / C++ or MATLAB, but can't seem to figure this out even after searching prior posts.

Here is the relating code segment, which is to be used with a button:

Private Sub Layout_populate__buttn_Click()

Application.ScreenUpdating = False ' Temporarily Disable Screen Updating:

' Define Variables:
' ========================================================
' Worksheets:
' ------------
Dim Main_populated__sheet As Worksheet ' Main (current) worksheet to work off of
Dim Linear_Interpolation_1__sheet As Worksheet ' Sheet that data is saved onto and to be worked with in this example

' Iterators:
' ------------
Dim Count_i As Integer ' Loop position in Cartesian x-direction
Dim Count_j As Integer ' Loop Position in Cartesian y-direction
Dim Iterator_placement_A As Integer ' Iterator defined during the main code run

' Other Variables defined in the rest of the code:
' ----------------------------------------------
Dim Selection_total_range As Integer
Dim Column_A As Integer
Dim Column_B As Integer

' Initialize Variables:
' ========================================================
Linear_Interpolation_1__sheet = Sheets("Linear_Interpolation_1")

Count_i = 0
Count_j = 0

' The following are defined in the code prior to this point,
' eliminating a .Range("X#:Y#") solution
' --------------------------------------------------
Iterator_placement_A = 16
Selection_total_range = 177
Column_A = 2 ' Column "B"
Column_B = 6 ' Column "G"


' Fill Empty Cells with Zeroes:
' ========================================================

With Linear_Interpolation__sheet_1 ' Select Worksheet to do work on

For Count_i = Column_A To Column_B ' Columns to loop through

For Count_j = Iterator_placement_A To Selection_total_range Step 1 ' Rows to loop through

If Linear_Interpolation__sheet_1.Range(Cells(Count_j, Count_i)).txt = "" Then

Linear_Interpolation__sheet_1.Range(Cells(Count_j, Count_i)).Value = 0
' If cell looked at is blank, fill it with a zero

End If

Next Count_j
Next Count_i
End With

' Re-Enable Screen Updating:
' =====================================================
Application.ScreenUpdating = True

End Sub

If possible please explain what differences are present if you find a solution to this problem. I actually want to learn and understand what it is my code is doing rather than 'just making it work.'

This perhaps should be in a seperate post, but why is it that in Excel the traditional Cartesian X and Y planes are reversed? I have seen it in a few places where one will select:

.Cells(row_position, column_position)

This just seems rather confusing and unintuitive to me.


Thanks in advance for any help!

~ Sarvazad


EDIT: I have attached the quoted region in a quick text file for cleaner viewing, since all the spacing does not appear on the web page.
Issue_w_2D_for_loop_(example_file).txt

[xladept]

Try:

Please Login or Register  to view this content.

Matrices are designated r,c ?!

If you've been ooping then you're not used to having to set objects since in oop everything is already an object - I think that's probably the biggest difference you're facing

[Sarvazad]

Hi xladept,

Thank you for the reply. I have tried your suggestion, and even furthered this with:

For Count_i = 2 To 6 Step 1

However, I am still having no luck. Any other ideas?


I have considered other types of loops, but the core issue would still be the error:

" Run-time error '1004': Method 'Range' of object '__Worksheet' failed " at the line:

If Linear_Interpolation__sheet_1.Range(Cells(Count_j, Count_i)).txt = "" Then

[Yraen]

Here's your existing code fixed. Everything in red is what I added or changed.

Please Login or Register  to view this content.

[Sarvazad]

Yraen, thank you very much! That was an absolutely perfect reply, and directly fixed my problems!

I had rushed to make a tinier code to illustrate the problems I was having but unfortunately missed typing the "Set" command earlier in the code. The set actively being used was just to illustrate that the work being done was not on the current sheet, right in the code for anyone who may refer to this later.

I had no idea you could directly use the ".Cells()" command on its own like that without the range. Between that and changing the ".txt" to a ".value" fixed everything.

I have to ask, how did you insert your code changes like that so neatly? It would be nice to not force anyone to read code in italics and without the white space organization in the future haha.

Also, when is it appropriate to use the '.txt' versus the '.value' command? This clearly caused a problem here as well, and would like to not make a similar mistake.

Thank you again!

[Yraen]

Thanks for the rep.

For the code tags, go to the advanced editor, paste your code, select it and click the pound (#) sign above. Or use [ code ] and [ /code ] without the spaces.

I'm unaware of any .txt field in excel VBA. I'm not saying it doesn't exist, just I've never used it. .Value is used for the value of the cell, .Formula for the formula, .FormulaR1C1 for the formula using the R1C1-style notation.