Help with Range Selection

[codye101]

I am currently trying to create a macro that finds a cell with a certain word and selects all cells in a row only if they contain data and replaces that data. This is what I have so far.

If Trim(rngCell) = "Bead Lot" Then
rngCell.Offset(0, 1).Select
Range(rngCell.Offset(0,1), ActiveCell.End(xlToRight)) = "14381"
End If

Currently it works but only if there is more than 1 column of data after the cell containing "Bead Lot" otherwise it fills the entire row with data instead of only replacing existing data.

 

[Joe4]

Welcome to the Board!

Assuming these cells with data are hard-coded (and not formulas), how about this:

If Trim(rngCell) = "Bead Lot" Then
    Rows(rngCell.Row).SpecialCells(xlCellTypeConstants, 23) = "14381"
End If

 

[codye101]

Welcome to the Board!

Assuming these cells with data are hard-coded (and not formulas), how about this:

If Trim(rngCell) = "Bead Lot" Then
    Rows(rngCell.Row).SpecialCells(xlCellTypeConstants, 23) = "14381"
End If

Oh that seems to work pretty well but it's replacing the "Bead Lot" cell with the number also now.

 

[Joe4]

Oh that seems to work pretty well but it's replacing the "Bead Lot" cell with the number also now.

It wasn't quite clear that you wanted to leave that value. You can just set it back, i.e.

If Trim(rngCell) = "Bead Lot" Then
    Rows(rngCell.Row).SpecialCells(xlCellTypeConstants, 23) = "14381"
    rngCell = "Bead Lot"
End If

 

[codye101]

It wasn't quite clear that you wanted to leave that value. You can just set it back, i.e.

If Trim(rngCell) = "Bead Lot" Then
    Rows(rngCell.Row).SpecialCells(xlCellTypeConstants, 23) = "14381"
    rngCell = "Bead Lot"
End If

Oh that worked, thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!