Excel 2013 VBA: Set range as range (offset to next empty cell in column)

[jedwardo]

Hello Again,

I'll try not to be too longwinded about this. Here's some pics:

This is Sheet5 "Quantities"

[/IMG]

I'm wanting to populate column A on sheet5 with the appropriate bid lettering/numbering from Sheet6 "Estimate". Here's a pic of that one:




This on is Sheet6 "Estimate"

[/IMG]


Here's the code I've been trying to make work:

Sub Exasperation()

Dim rngOff As Range      [COLOR=#0000ff]' sheet6 "Estimate" range (offset of rngBold, column C)[/COLOR]
Dim rngBold As Range    [COLOR=#0000ff] ' sheet6 "Estimate" range (all of column B)[/COLOR]
Dim bCell As Range
Dim ws5 As Worksheet
Dim ws As Worksheet
Set ws5 = Sheet5
Set ws = Sheet6
Set rngBold = ws.Range("B2:B500")
[COLOR=#ff0000]Set rngOff = ws.Range("rngBold.Offset(0, 1)" & Rows.Count).End(xlUp)  [/COLOR][COLOR=#0000ff]'this is what I can't make work, supposed to be from rngBold to next empty cell in column C on "Estimate[/COLOR]


For Each bCell In ws5.Range("B6:B60")   
    With bCell

        If bCell.Value = rngBold And rngBold.Font.Bold = True And rngOff = bCell.Offset(0, 1).Value Then

            bCell.Offset(0, -1).Value = rngBold.Offset(0, -1).Value & rngOff.Offset(0, -1).Value

        End If
    End With
Next bCell


End Sub

I think I have it worked out for the most part if I can just figure out how to set that one range highlighted in red.

So basically, I need to set a range as rngBold.offset(0, 1) to next empty cell in that column but can't seem to make it work.

 

[Norie]

Perhaps.

Set rngOff = ws.Range(rngBold.Offset(0, 1), ws.Cells(Rows.Count, rngBold.Offset(0, 1).Column).End(xlUp))

 

[jedwardo]

Awesome, that did it. Now it's throwing an error a little further down on the highlighted section. Run Time error 13, Type Mismatch

Sub Exasperation()

Dim rngOff As Range
Dim rngRoom As Range
Dim rngBold As Range
Dim bCell As Range
Dim ws5 As Worksheet
Dim ws As Worksheet
Set ws5 = Sheet5
Set ws = Sheet6
Set rngRoom = ws5.Range("B6:B60")
Set rngBold = ws.Range("B2:B500")
Set rngOff = ws.Range(rngBold.Offset(0, 1), ws.Cells(Rows.Count, rngBold.Offset(0, 1).Column).End(xlUp))


For Each bCell In ws5.Range("A6:A60")
    With bCell
[COLOR=#0000ff]        If bCell.Value = rngBold And rngBold.Font.Bold = True And [/COLOR][COLOR=#0000FF]rngOff = [/COLOR][COLOR=#0000ff]bCell.Offset(0, 1).Value Then[/COLOR]
            bCell.Offset(0, -1).Value = rngBold.Offset(0, -1).Value & rngOff.Offset(0, -1).Value
        End If
    End With
Next bCell


End Sub

 

[jedwardo]

Managed to get rid of all the errors by changing all the .Value to .Text, it just doesn't seem to do anything.

Sub Exasperation()

Dim rngOff As Range
Dim rngBold As Range
Dim bCell As Range
Dim ws5 As Worksheet
Dim ws As Worksheet
Set ws5 = Sheet5
Set ws = Sheet6
Set rngBold = ws.Range("B2:B500")
Set rngOff = ws.Range(rngBold.Offset(0, 1), ws.Cells(Rows.Count, rngBold.Offset(0, 1).Column).End(xlUp))


For Each bCell In ws5.Range("A6:A60")
    With bCell
        If bCell.Text = rngBold.Text And rngBold.Font.Bold = True And bCell.Offset(0, 1).Text = rngOff.Text Then
            bCell.Offset(0, -1).Text = rngBold.Offset(0, -1).Text & rngOff.Offset(0, -1).Text
        End If
    End With
Next bCell


End Sub

 

[Norie]

rngBold is a range of almost 500 cells/values whereas bCell is one cell/value, you can't compare the two directly as you are trying to do.

It's the same for bCell.Offset(0,1) and rngOff.

The reason changing Value to Text is because Text will only return Null from a range consisting of multiple values.

What are you actually trying to do with the code?

 

[jedwardo]

Ah, I was splitting it up into smaller pieces to test them out and starting to realize I probably needed to scrap this idea lol. That makes sense. I was trying to get the number/letter values from the second picture to row A in the appropriate cells in the first pic (sheet5). So in this example in the first picture the value of A18 would be "A1a", the bid position it held ("A1") from A9 of the other sheet and the operation position ("a") from B10. The main goal being to turn this data page into a paint quantites reference page the customers (and I) could use for being prepared at the beginning of the job.

Was thinking it seemed clear cut but if this is a monster undertaking just to shift some letters around I may move it down on the priority level