Excel, how to add IF into a loop

2022-05-04 10:29 Elias imported from Stackoverflow

I have a IF-Statement, and I need to loop it throug column F.

This loop checks for the word "empty" in column F and if found, it gets entered into columns G too. In column H the current date gets added, if it was not already in it. If F and G have "empty" in it, and H a date, the If-Statement gets ended.

If Range("F2").Value = "empty" And Range("G2").Value = "" Then
Range("G2").Value = "empty"
ElseIf (Range("F2").Value = "empty" And Range("G2").Value = "empty") And Range("H2").Value = "" Then
Range("H2") = Date
ElseIf (Range("F2").Value = "empty" And Range("G2").Value = "empty") And Range("H2").Value <> "" Then
End If

Can someone help me to add this into a loop, that goes trough the lines?

It manly needs to go trough line 2 to 1500.

Any help would be apprechiated.

Kind regards.

3 answers

answered 2022-05-04 10:36 Sphinx

Try something like this

    Dim i as long
    For i = 2 to 1500 
        If Range("F" & i).Value = "empty" And Range("G" & i).Value = "" Then
           Range("G" & i).Value = "empty"
        ElseIf (Range("F" & i).Value = "empty" And Range("G" & i).Value = "empty") And Range("H" & i).Value = "" Then
           Range("H" & i) = Date
        ElseIf (Range("F" & i).Value = "empty" And Range("G" & i).Value = "empty") And Range("H" & i).Value <> "" Then
           'do something 
        End If
    Next i

answered 2022-05-04 10:43 VBasic2008

Nested Statements in a Loop

Sub NestedStatements()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range: Set rg = ws.Range("F2:H1500")
    
    Dim rrg As Range
    
    For Each rrg In rg.Rows
        If CStr(rrg.Cells(1).Value) = "empty" Then
            Select Case CStr(rrg.Cells(2).Value)
            Case ""
                rrg.Cells(2).Value = "empty"
            Case "empty"
                If CStr(rrg.Cells(3).Value) = "" Then
                    rrg.Cells(3).Value = Date
                End If
            End Select
        End If
    Next rrg
    
End Sub

answered 2022-05-04 10:44 Ike

I would create a single sub to do the job - to which you pass the range that should be checked:

Option Explicit

Private Const colF As Long = 6
Private Const colG As Long = 7
Private Const colH As Long = 8

'-->> this is an example of how to call the sub
Sub test_checkColumnsFtoH()
checkColumnsFtoH ThisWorkbook.Worksheets("Table1").Range("A1:I500")
End Sub

'-->> this is your new sub
Sub checkColumnsFtoH(rgToBeChecked As Range)
Dim i As Long
With rgToBeChecked
    For i = 2 To .Rows.Count
        If .Cells(i, colF).Value = "empty" And .Cells(i, colG).Value = "" Then
            .Cells(i, colG).Value = "empty"
        ElseIf (.Cells(i, colF).Value = "empty" And .Cells(i, colG).Value = "empty") _
                And .Cells(i, colH).Value = "" Then
            .Cells(i, colH) = Date
        End If
    Next
End With

End Sub

I am using the cells property to avoid string concatination ("H" & i)
you don't need the last elseif - as nothing happens there.

See also questions close to this topic

.Body & .HTMLBody Indent and Default Signature Issue
I've set up a macro to send an e-mail through Outlook.

.Body is read from a cell inside the file with indents. Since the value will change depending on the usage, I need to reference that cell for the body.

However, there rises 2 issues using .HTMLbody I lose indents which are constructed with CHAR(10) but I keep the default HTML signature.

When using just .BODY indents are displayed are correctly however the default signature is not constructed as HTML and I lose the images.

How should I go about fixing this issue?

My code:
```
sig = .HTMLBody
body = xlSht.Range("B4").Value
    .To = xlSht.Range("B2").Value
    .CC = ""
    .Subject = xlSht.Range("B1").Value
    .body = body & sig
    .Display
```
I'd really appreciate your assistance.

Thanks.

Yahoo Finance no longer returns VBA cookie request for .getResponseHeader("Set-Cookie")

The following Excel VBA code segment has worked for years, but stopped working around 28 Apr 2022. I receive the responseText, but the .getResponseHeader("Set-Cookie") returns null.

Set httpReq = New WinHttp.WinHttpRequest

DownloadURL = "https://finance.yahoo.com/lookup?s=" & stockSymbol
With httpReq
    .Open "GET", DownloadURL, False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send
    .waitForResponse
    response = .responseText
    cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
End With

export excel rows to individual json files in python

My excel file has 500 rows of data. I am trying to get 500 individual JSON files. Each file should have data only from 1 row. Thank you in advance.

import json
import pandas
excel_data_df = pandas.read_excel("F:/2/N.csv.xlsx", 
sheet_name='Sheet1')
json_str = 
excel_data_df.to_json(orient='records')
for idx, row in enumerate(json_str):
fpath = str(idx) + ".json"
with open(fpath, "w+") as f:
json.dump(row, f)

VBA: Creating a class property that is an array of dictionaries
In Microsoft Excel VBA I need to create a class that has two properties, "Name" and "Holdings". "Name" is just a string so the code for that is easy. But I need "Holdings" to be a variable length array containing dictionary objects. So this is what I wrote:
```
Private mName As String
Private mHoldings() As Scripting.Dictionary

Public Property Let Name(vName As String)
      mName = vName
End Property
 
Public Property Get Name() As String
      Name = mName
End Property
 
Public Property Set Holdings(vHoldings() As Scripting.Dictionary)
      Set mHoldings = vHoldings
End Property
 
Public Property Get Holdings() As Scripting.Dictionary
      Set Holdings = mHoldings
End Property
```
When I try to compile it gives me this error: "Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalide Set final parameter.

What I doing wrong?

Related questions

Excel, how to add IF into a loop

3 answers

Nested Statements in a Loop

See also questions close to this topic