Creating VBA If Then Loop through Range of Cells

Question

I have two bits of codes that work seperatley, but I can't figure out how to combine them. I want to go through a column of numbers and test if they are >= 100 and then change the result if true.

I don't know how many rows this column will be so I have used the following line in another part of this Macro.

Range("D1:D" & Final & "").

And I have successfully tested a single cell by directly specifiying it like so:

Sub CellTest()
Dim cell As Integer
cell = Range("D7").Value
If cell >= 100 Then
Range("D7").Value = "NoSplit"
End If
End Sub

Can anybody help me combine these so it iterates through every row and tests the value?

Update:

This spreadsheet is downloaded via a script on a webpage and has a different name each time. There are a few things I will know ahead of time such as...

There is only 1 sheet
The column in question is column D
column D will only have numbers with no decimal points
I will not know how many rows in this column ahead of time

I see that there's an answer for you listed, but will bring up some basics for looping. A loop will iterate through an array of values; that's it. What you do with that can be simple or complex. Let's use "i" as a variable to account for iterating... your data can be a range of cells in column A (1) from rows 1 to 10 and you want to go 1 by 1, e.g., 1 to 2, 2 to 3, 3 to 4, etc. You can generate a for loop to account for this, such that For i = 1 to 10 Step 1, then you can utilize the iterated value (rows) using cells(i,1).value = "" inside of your loop. — Cyril
– Cyril, Commented Jan 10, 2019 at 17:07
@Cyril I saw the demo of that method, but was unsure how to utilize it since I don't know how many rows. — FreeSoftwareServers
– FreeSoftwareServers, Commented Jan 10, 2019 at 21:03

Doug Coats · Accepted Answer · 2019-01-10 21:27:26Z

2

Public Sub testforhundred()
    Dim rcell As Range, rng As Range
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("D1:D" & ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Count)
    For Each rcell In rng.Cells
        If rcell.Value >= 100 Then rcell.Value = "NoSplit"
    Next rcell
End Sub

this combines them easily. However if the row count is huge itll be time consuming due to interacting with the sheet object

edited Jan 10, 2019 at 21:27

answered Jan 10, 2019 at 17:01

Doug Coats

7,2279 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

FreeSoftwareServers Over a year ago

Hmm the sheet name will change per use, so having it hard coded is a no go :(

cybernetic.nomad Over a year ago

Will it be a specified sheet or the ActiveSheet?

urdearboy Over a year ago

@FreeSoftwareServers, your question doesn't mention anything about dynamic sheets. If this is a criteria, it needs to be listed in your question. Even with your comment, it's not clear what sheet. Do you want to loop this code through all sheets? Some sheet? Will it just run on the selected sheet? Please edit your question and specify any criteria

Cyril Over a year ago

@FreeSoftwareServers For all extensive purposes, an answer can explain how to do something (which doesn't have to be a copy-pasteable response), where it is an understood assumption that persons asking questions have some understanding of the subject matter when conversing with experts. Essentially, the poster of a question must be able to recognize the point made by the expert answering said question.

cybernetic.nomad Over a year ago

With this, you should be able to apply Doug's answer to your code. (Don't forget to mark it as correct if it solves your issue).

|

Collectives™ on Stack Overflow

Creating VBA If Then Loop through Range of Cells

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related