Excel Tips - Find the last cell for non contiguous range

Suppose I have a range consisting of not-necessarily contiguous cells in the same columnn, e.g.,

Set rMyRange = Range("$A$1:$A$3, $A$8, $A$10")

How do I directly access the Nth cell in the range?

For example, I tried rMyRange.cells(4).address...but I get $A$4 instead of $A$8, which is what I want.

I was able to pick off the fourth item using a For loop (e.g.,

For each rMyCell in rMyRange

But this is tedious. Is there a way to directly read the fourth cell?

Is it possible, through VBA, to select the top right cell in a non-contiguous range? For example, I have defined A1, A3, and A5 as a range. How could I tell Excel to select A5 from within that range?

I am using Excel 2007.

Please let me know if you don't understand my question and I'll attempt to clarify!

Thanks,
Tom

Range("B8") contains text that I want to use to name my range.
Range is form B8 to B14 and selected by selecting B8 and then
Range(Selection, Selection.End(xlDown)).Select
When doing above selection Range b8 is the active cell.
I want to define the name of the selected range with the value of b8
Whatever code I have tried brings up error 1004 name not valid.
What is the proper code for this?

Hi,
I have following code inside a module:
VB:

Sub PutRightBorder() 
    With Range("B4:B11,D4:D11,F4:F11").Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



It works fine if called from a Button, another procedure or if debuging(F8).

Here is the problem:
If I call the procedure from the "Before_Print" event,
VB:

Private Sub Workbook_BeforePrint(Cancel As Boolean) 
    PutRightBorder 
     
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



it pops up an error in the With line.
VB:

With Range("B4:B11,D4:D11,F4:F11").Borders(xlEdgeRight) 'Here stops the debuger and pops the error.

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Has anybody an idea what is wrong with the code; why is it behaving like that?
I appreciate your help very much.

Saludos

I have many formulas that look like this:

Cell AS22
=IF('AAccess Query'!D6="","",IF(AW10="",'Access Query'!D6,AW10))

The next formula looks the same but different values, like this:

Cell AS28
=IF('Access Query'!D7="","",IF(AW11="",'Access Query'!D7,AW11))

This pattern continues down column "AS" but not one row after the other. There is 5 blank rows between each cell (in column "AS") before the next cell with the above formula format.

I need all the formulas in column "AS" to look like this (relative to the correct cell references):

Cell AS22
=IF(AND(AW10="",'Access Query'!D6=""),"",IF(AW10="",'Access Query'!D6,AW10))

Cell AS28
=IF(AND(AW11="",'Access Query'!D7=""),"",IF(AW11="",'Access Query'!D7,AW11))
Had these cell been one row after the other i could just copy the formulas down.

Could change them all manually but don't really want the trouble.

Any help would be appreciated

I would like to be able to copy formulas using something like "fill by
example".
a very simplified example would be
B2 =A4, B3=A7, B4=A10 etc.
I would like to be able to drag this series down column B.


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Dear Old Chums ,
See I am still at it..made a little progress but going on ..here is the mutilated code I want to edit ..attached is the dummy sheet sheet .Just need to sort out the range issue for now ..

Code:

Sub DataEnterprise()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim myRange As Range
    Dim R1 As Range
    Dim R2 As Range
    Dim R3 As Range
    Dim LR As Long
    Dim eRng As Range
    Dim eCell As Range
    myRange.Formula = "=RAND()"
    'Find last row with data in Column N (Email Addresses)
   ' LR = Range("N" & Rows.Count).End(xlUp).Row
    'LR = Range("M" & Rows.Count).End(xlUp).Row
    LR = Range("L" & Rows.Count).End(xlUp).Row
    

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    
    'Set the range of Email Addresses
    'Set eRng = Range("N2:N" & LR)
    Set eRng = Range("L2:L" & LR)
    
    'Cycle through each cell in Column N (each Email Address)
    For Each eCell In eRng

        If eCell.Value <> "" And _
                eCell.Offset(0, -2).Value = "Active" _
                Or eCell.Offset(0, -2).Value = "Active " Then

            '     If cell.Value Like "?*@?*.?*" And _
                  '         (Cells(cell.Row, "E").Value) = "Active" Then


            'This is the Table Headers
            'Set R1 = Range("E1:K1")
            Set R1 = Range("C1,e1,i1")
            
            
            'This is the Table Detail
            'Set R2 = eCell.Offset(0, -9).Resize(1, 7)
            Set R2 = eCell.Offset(0, -9).Resize(1, 3)
            
            
            'This is the Bottom Border of the Table
            'Set R3 = Range("E1").End(xlDown).Offset(1, 0).Resize(1, 7)
            Set R3 = Range("C1").End(xlDown).Offset(1, 0).Resize(1, 3)
            
            
            'This creates the Table from above
            Set myRange = Union(R1, R2, R3)


            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = eCell.Value
                '.CC = eCell.Offset(0, 1)
                .CC = "OverDue"
                .BCC = eCell.Offset(0, 2)
                '.Subject = "Balance: " & eCell.Offset(0, -13) & " - " & eCell.Offset(0, -12) & " - " & eCell.Offset(0, -11)
                .Subject = "Over Due Invoices: " & eCell.Offset(0, -11) & " - " & eCell.Offset(0, -10) & " - " & eCell.Offset(0, -9)
                '.HTMLBody = "<H4>Dear " & eCell.Offset(0, 3).Value & "</H4>" & _
                        '"Hope you are fine<BR>" & _
                        '"<BR>Please find below the current status<BR>" & _
                        '"of your account" & _
                        '"<H4><U>Balance Summary</U></H4>" & _
                        'RangetoHTML(Rng) & _
                        '"<BR>Today's position is:<h3><font color =red > over the limit</font></h3>Request you to make immediate payment.For queries please revert or call, I will be glad to assist. <BR>" & _
                        ''"<BR>Best Regards<BR>" & _
                        '"<H4>" & eCell.Offset(0, -10).Value & "</H4>" & _
                        ''"<H4>India</H4></BR>"
                    .HTMLBody = "<H4>" & [DE!B3] & " " & eCell.Offset(0, -1).Value & "," & "</H4>" & _
                    [DE!B4] & "<BR>" & "<BR>" & [DE!B5] & "<BR>" & [DE!B6] & _
                    "<H4><U>" & [DE!B7] & "</U></H4>" & RangetoHTML(myRange) & _
                    "<BR>" & [DE!B9] & "    " & Format(Date, "mmmm  dd , yyyy") & _
                    "   " & [DE!C9] & "<h4><font color =red >" & "<u>" & [DE!B10] & _
                    "   $ " & eCell.Offset(0, -3).Value & "</u>" & "</h4>" & "</H5> </font> </h5>" & [DE!B11] & "<BR>" & "</H5>" & [DE!B12] & _
                    [DE!B13] & "</H5>" & [DE!B14] & "</H5>" & "<H4>" & [DE!B15] & "<H4>" & "<H4>" & [DE!B16] & "</H4>" & "<H4>" & [DE!B17] & "</H4>" & "<H4>" & [DE!B18] & "<H4>" & "<H4>" & [DE!B19] & "<H4> "
                    
                    
                    
                    
                    ' Sheets name must be the same ! body and not Body !

                '.Send  'Or use
                .Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next eCell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub
Function RangetoHTML(Rng As Range)
'29/01/2011, RB: sourced from http://www.rondebruin.nl/mail/folder3/row2.htm
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    Rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
            SourceType:=xlSourceRange, _
            Filename:=TempFile, _
            Sheet:=TempWB.Sheets(1).Name, _
            Source:=TempWB.Sheets(1).UsedRange.Address, _
            HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
            "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Thanks in advance ...T-10 ,B ,J
Regards
N

before reading note I have not tried to use 100% accurate terminology - it's in donkey speak - ie written in a way I *think* makes sense and won't cause confusion...

c/o daddylonglegs we know that we can use CHOOSE to create contiguous "ranges" from non-contiguous ranges where those ranges share the same dimensions, eg:

Code:

=VLOOKUP(A1,CHOOSE({1,2},Z1:Z100,C1:C100),2,0)

the above in essence being a right to left VLOOKUP

As illustrated by daddylonglegs here in post # 5 we know that we can also use CHOOSE to construct contiguous "ranges" from non-contiguous ranges with differing dimensions by processing each cell individually, eg:

Code:

=MATCH(A1,CHOOSE({1,2,3,4,5},B1,B2,B5,B6,B7),0)

so here we are conducting a MATCH against 2 non-contiguous ranges of different dimensions (B1:B2, B5:B7) but as a single contiguous "range" of 5 cells.

So here's my question... can we create a single contiguous "range" from multiple non-contiguous ranges of differing dimensions but on an area by area basis rather than cell by cell ?

Please read post in full before posting suggestions restrictions etc...

Consider:

Code:

A1: 
apple

B1:B10
cat
dog
banana
elephant
apple
giraffe
monkey
apple
fish
apple

The aim is to replicate the earlier MATCH but rather than using CHOOSE on a cell by cell basis we want to use the Areas (B1:B4, B6:B7, B9:B10) ... in pseudo terms:

Code:

=MATCH(A1,(B1:B4,B6:B7,B8:B10),0)

A CHOOSE construct here won't work:

Code:

=MATCH(A1,CHOOSE({1,2,3},B1:B4,B6:B7,B9:B10),0)

as we will end up with #N/As given different dimensions...


For sake of clarity - given the sample values the answer we expect is 8 given "apple" is the 8th item to appear within the 8 valid items.


To reiterate - the aim is to create a single contiguous array of 8 items for use in a single MATCH function with following restrictions:

Quote:

a) we are not looking to operate 3 different tests (1 per area)

b) we are not looking to create an array of 10 items in which the 2 values to be discounted are erased/modified (B5, B8)

c) UDF / VBA is not permitted

d) 3rd Party Function are not permitted (eg ARRAY.JOIN morefunc)

edit:
e) solution should be scalable (within reason) though not nec. truly dynamic

I don't think it's possible given the fact the various arrays are of different dimensions but I'm perhaps not thinking outside the box enough.
I would love to see a workaround if one exists however inefficient / ugly / long winded it might be.

I am working on a macro that will automatically populate a few worksheets using VLOOKUP or HLOOKUP formulas. I have come to a point where I must now populate a column (using vlookup formulas), but this column will vary each time the macro is run.

The worksheet in which the data will be placed has location codes in column A (starting at A2) and Week Codes (Week 1, Week 2, etc) in Row One (starting in B1). My issue is how to create code that figures out the appropriate week's column. I already have users input the week number (i.e. users input the number 3 for Week 3), so I have been trying to use that input (which is stored in the variable "WeekCodeEntered") in order to create "Week 3" and then compare this against the 53 cells containing a week code (B2:BB2). Once I am able to find the match, I would need to retrieve the Column Number assigned to the cell containing the matching week code.

I am truly lost.

Any assistance is much appreciated!

Thanks,
Godfrey

Hi,

I am trying to find a vba code that will find blank cells in a certain column (Column O), and replace these cells with a formula that includes the median of cells above (up until the next blank cell)

for instance, cells O3:O15 are filled in, O16 is blank, I want a code that can find the blank O16 cell, and in that cell calculate the median of O3:O15.

I have about 150,000 rows, and there is not a uniform spacing between the blank cell ranges.

Thank you in advance!!