CODEWITHSUNDEEP
excelvbavariablesrange
user1542821
user1542821

Reputation: 15

How can I access non-contiguous content of a range variable without using arrays

RE-RECLARIFIED: I am a excel vba newbie, so bear with me...

I need to access a single cell in a non-contiguous range variable and alter its value.

Is there no sequential way to loop through a non-contiguous range variable?

For example: 

rng = Range("d1:d6, d12")

There are 7 cells in this range but I can't loop through the cells in this range because the "cells" function sees the 7th cell as d7. Or can I in some other way?

I can't use FOR EACH because I need to use another variable other than the range itself to jump through range and change values in it.

So here is a non-functioning example of what I'm looking to do: 

' If I could access a range like an array

    rng(1) = Application.InputBox(prompt:="Enter customer's name: ", Title:="CUSTOMER NAME", Type:=2)
    rng(2) = Application.InputBox(prompt:="Enter travel out date: ", Title:="TRAVEL OUT DATE", Type:=1)
    rng(3) = Application.InputBox(prompt:="Enter travel back date: ", Title:="TRAVEL BACK DATE", Type:=1)
    rng(4) = Application.InputBox(prompt:="Enter number of technicians: ", Title:="TECHNICIANS", Type:=1)
    rng(5) = Application.InputBox(prompt:="Enter number of engineers: ", Title:="ENGINEERS", Type:=1)
    rng(6) = Application.InputBox(prompt:="Enter location: ", Title:="LOCATION", Type:=2)
    rng(7) = Application.InputBox(prompt:="Enter todays date: ", Title:="TODAY'S DATE", Type:=1)

I don't want to use an array because I don't just want to manipulate the values of the cells separately, I want to alter the value in the cell and have it reflected in that cell without having to go through the process of reloading the array contents into the range, which presents me with the same issue again anyway.

Any suggestions?

Upvotes: 0

Views: 1888

Answers (3)

Tim Williams
Tim Williams

Reputation: 166156

Non-regular or non-contiguous ranges have Areas representing regular rectangular sub-ranges. EDIT: note that two Areas may overlap, so watch out for this if using the code below...

Dim a as range, c as range

For each a in yourRange.Areas
    For each c in a
      'something with c
    Next c
Next a

EDIT: you can use a function to get the n'th cell from your range.

Note this will return Nothing if you ask for an index beyond the range's size.

'get the n'th cell from a non-contiguous range
'probably not efficient for large ranges...
Function NthCell(rng as Range, n as integer) as Range
    Dim i as integer, c as Range
    Dim rv as Range

    i=1
    for each c in rng.Cells
        if i=n then
            Set rv=c
            Exit For
        end if
    Next c

    Set NthCell=rv
End Function


'Sample usage:
Set rng = Range("D1:D6,D12")
NthCell(rng, 1).Value = ....
NthCell(rng, 7).Value = ....

Upvotes: 0

Alan
Alan

Reputation: 46

Hmmm, how about this?

Sub test()

  Dim Arr() As String         ' dynamic array,
  ReDim Arr(Selection.Count)  ' ... properly sized
  i = 0
  For Each c In Selection
    i = i + 1
    Arr(i) = c.Address        ' save each cell address
  Next c

  ' now we can reference the cells sequentially
  Range(Arr(1)) = Application.InputBox(prompt:="Enter customer's name: ", Title:="CUSTOMER NAME", Type:=2)
  Range(Arr(2)) = Application.InputBox(prompt:="Enter travel out date: ", Title:="TRAVEL OUT DATE", Type:=1)
' ...

End Sub

Upvotes: 1

jrad
jrad

Reputation: 3190

If you highlight the cells you want to access on the spreadsheet itself, you can do this:

Sub accessAll()
    Dim cell As Range
    For Each cell In Selection
        (do something here)
    Next cell
End Sub

This takes each cell that you've highlighted and does something to it.

Upvotes: 1

Related Questions