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"

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"

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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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
76,344
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,947
Members
430,329
Latest member
asmith75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top