Using IF function with text

Question

I'm trying to make a VBA macro that will look in Column D for a specific value and input text into 3 others. I believe that I am close to having the answer I need, but I'm not sure what I'm missing to get this to work.

Here's what I have so far:

Dim rng As range
Dim i As Long

'Set the range in column D you want to loop through
**Set rng = range("D3:D")**
For Each cell In rng
    'Test cell for CR
    If cell.Value = "CR" Then
        'write to adjacent cell
        cell.Offset(0, 3).Value = "CREDIT"
        cell.Offset(0, 5).Value = "CREDIT"
        cell.Offset(0, 6).Value = "CREDIT"
    End If
Next
**Set rng = range("G3:G")**
For Each cell In rng
    'test for empty cell
    If cell.Value = "" Then
        'write to adjacent cell
        cell.Offset(0, 0).Value = "FREIGHT"
        cell.Offset(0, 2).Value = "FREIGHT"
        cell.Offset(0, 3).Value = "FREIGHT"
    End If
Next

End Sub

The lines with ** are where I am running into errors.

Any help is greatly appreciated!

What's the last cell in the range? You can't have just a start cell with no end cell. You need, say, Set rng = Range("D3:D"&lastRow) (where lastRow is say Dim lastRow as Long // lastRow = Rance("D"&rows.count).End(xlUp).Row), or Set rng = Range("D3:D100"). — BruceWayne
– BruceWayne, Commented May 29, 2018 at 14:35
You also do full qualify what sheet youre using either. Unless your code is behidn a sheet object. — Doug Coats
– Doug Coats, Commented May 29, 2018 at 14:39
Thank you for pointing that out--I added in the "lastrow" because this will vary. The code is all based off of this sheet, so I don't believe I need to qualify it as I haven't needed to with the other pieces of code attached to it. — F.Angel07
– F.Angel07, Commented May 29, 2018 at 14:43

Vityata · Accepted Answer · 2018-05-29 14:49:52Z

3

Try this:

Set rng = Range("D3", Cells(Rows.Count, "D").End(xlUp))

edited May 29, 2018 at 14:49

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

answered May 29, 2018 at 14:38

Olly

7,9021 gold badge23 silver badges39 bronze badges

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

Comments

Vityata · Accepted Answer · 2018-05-29 14:46:10Z

0

This is a way to declare a range, based on a given cell and its End(xlDown):

Option Explicit

Sub TestMe()

    Dim myRange As Range
    Dim firstCell   As Range
    Dim lastCell As Range

    Set firstCell = Worksheets(1).Range("A12")
    Set lastCell = firstCell.End(xlDown)
    Set myRange = Range(firstCell, lastCell)

End Sub

The idea is that ever two cells on a given worksheet can define a range. Thus, by defining them, the range gets defined as well.

The two cells are firstCell and lastCell. The interesting part is that you need to declare the parent object (worksheet) of firstCell only, then the lastCell and myRange use the same one.

answered May 29, 2018 at 14:46

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

Using IF function with text

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related