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

jedwardo

Board Regular
Joined
Aug 21, 2012
Messages
122
Hello Again,

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

This is Sheet5 "Quantities"

[IMG]http://i58.tinypic.com/20kalvc.png[/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]http://i61.tinypic.com/2h4yumg.png[/IMG]


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

Code:
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Perhaps.
Code:
Set rngOff = ws.Range(rngBold.Offset(0, 1), ws.Cells(Rows.Count, rngBold.Offset(0, 1).Column).End(xlUp))
 

jedwardo

Board Regular
Joined
Aug 21, 2012
Messages
122
Awesome, that did it. Now it's throwing an error a little further down on the highlighted section. Run Time error 13, Type Mismatch

Code:
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

Board Regular
Joined
Aug 21, 2012
Messages
122
Managed to get rid of all the errors by changing all the .Value to .Text, it just doesn't seem to do anything.

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 21, 2012
Messages
122
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top