Solved

Excel VBA Range question

Posted on 2014-09-25
12
71 Views
Last Modified: 2014-10-10
Hi,

How do I select Column B as the range for an action in column A?

Example: I want to insert a value in any blank cell in column A but only within the range of populated cells in column B.

Thanks in Advance,

swjtx99
0
Comment
Question by:swjtx99
  • 7
  • 5
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40344976
Why is this a VBA question? Why not just Excel's own functionality instead?

It sounds like you should use Data - Data Validation, and select the cells in column B as the range that the cells in column A can use.
0
 

Author Comment

by:swjtx99
ID: 40345030
Hi,

Thanks for your suggestion. I am forced to work in VBA for this task.

Regards,

swjtx99
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40345550
Then use Data Validation in VBA - something like this:
    With Columns(1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$B$1:$B$9999"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:swjtx99
ID: 40347179
Hi,

I've tried to apply your suggestion with no l luck. Perhaps if I  provided an example worksheet?

Regards,

swjtx99
Book1.xlsx
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40349901
Just insert it into a subroutine, like the attached.
Book1.xlsb
0
 

Author Comment

by:swjtx99
ID: 40351211
Hi Phillip,

I downloaded your attachment and ran it and it didn't work. I found/figured out a way to do it and have attached an example file.

    On Error Resume Next
    ' Look in column A
    With Columns(1)
        ' For blank cells, fill with !ERROR
        .SpecialCells(xlCellTypeBlanks).Value = "!ERROR"
    End With
    Err.Clear
Book1.xlsm
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40351683
Apparently your question was different to the one I thought you were asking.

Unfortunately your solution is not 100% effective - try inserting a value into B30, leaving B29 blank, and run your macro.
0
 

Author Comment

by:swjtx99
ID: 40356858
Hi Phillip,

You are correct, however on  my sheet, column B will never be blank therefore the solution works, even if the blanks in column A are not contiguous. Still, there may be a pitfall of which I am not aware so if anyone has that insight and/or a better solution, the points are still available.

Thanks in advance,

swjtx99
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40356865
for introw =1 to 9999
    if cells(introw,2)="" then
        cells(introw,1)="!ERROR"
    end if
next

Open in new window


Alternatively, do you really need a value? What about a color (use Conditional Formatting)?
0
 

Author Comment

by:swjtx99
ID: 40361338
Hi Philip,

Your code filled every blank in column A with "!ERROR" after the last row in column B. It should have filled only blanks in column A where Column B was not blank down to the last row in column B.

This seems to work:

Sub Macro1()
    Columns("A:B").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Value = "!ERROR"
End Sub

swjtx99
0
 

Accepted Solution

by:
swjtx99 earned 0 total points
ID: 40361395
Hi,

Just discovered a problem. Runtime error if there were no blanks. This seems to have solved that problem:

    Columns("A:B").Select
    On Error GoTo 500
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Value = "!ERROR"
500
0
 

Author Closing Comment

by:swjtx99
ID: 40372550
Need to close out this question and it seems as though I solved it myself. I don't know if this is allowed but I'll try it and see!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert Excel Column Headers to Rows 8 106
Excel - Sum Formula 3 80
Changing the date format from  dd/mm/yyyy to ddmmyy in excel 2010 8 75
Turn date into age 17 32
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn how to apply various conditional formatting in Excel 2013.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question