CODEWITHSUNDEEP
excelvba
F.Angel07
F.Angel07

Reputation: 23

Using IF function with text

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!

Upvotes: 1

Views: 74

Answers (2)

Olly
Olly

Reputation: 7891

Try this:

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

Upvotes: 3

Vityata
Vityata

Reputation: 43595

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.

Upvotes: 0

Related Questions