Arrays for newbies

[alexramo]

Hello again experts.
Although I always appreciate the expert advice and help I'm given here, I do want to become somewhat independent and resourceful!

I want to start adding arrays to my macros, and have never really understood the procedure. I am trying to learn on my own, through books and websites, but I seem to learn a lot faster by tinkering with code.

So, with all that said,
I was wondering if someone could use the spreadsheet example included, to build a simple macro, using an array to:

- if the value in column d is "Y" or "YES",
copy and paste columns A,G,and I onto sheet2.


I guess what I'm looking for is a simple formula/macro that utilzes an array to store the information, when a value is present(or true), and then paste that info into another page, for simplified viewing.

I know how to do it sloppily, searching through column D for my criteria, copying and pasting it into worksheet2, offsetting 1 row down, going back through column d on sheet1, looking for the next instance, and so on.

I appreciate anyones help, in getting me over this array hump, and helping me write cleaner and better vba code.

Thank you again,
-alex

p.s. can I attach the file from my computer or do I need to have an online storage account?

 

[Bong]

I don't understand your request, really, and anyway, it sounds a bit like asking this forum to do your work for you. Instead, here's a small routine that converts a binary string to a hexadecimal string using a lookup array:

Function b2h(bstr)
'convert binary string to hex string
    cnvarr = Array("0000", "0001", "0010", "0011", "0100", "0101", "0110", "0111", "1000", "1001", "1010", "1011", "1100", "1101", "1110", "1111")
'find number of HEX digits
    a = Len(bstr)
    ndgt = a / 4
    If (a Mod 4 > 0) Then
        MsgBox ("must be integer multiple of 4Bits")
        Exit Function
    End If
    hstr = ""
    For i = 1 To ndgt
        dgt = Mid(bstr, (i * 4) - 3, 4)
        For k = 0 To 15
            If (dgt = [red]cnvarr(k)[/red]) Then
                ix = k
            End If
        Next
        hstr = hstr & Hex(ix)
    Next
    b2h = hstr
End Function

_________________
Bob Rashkin

 

[SkipVought]

Hi,

I was wondering if someone could use the spreadsheet example included, to build a simple macro, using an array to:

- if the value in column d is "Y" or "YES",
copy and paste columns A,G,and I onto sheet2.

This requirement does not need an array. If you do a HELP on Copy, you'll find that the destination range can be referenced in the statment.

Get a VBA reference book and read up on arrays. It will explain WHEN they ought to be use and how.

Google in Tek-Tips on array.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[alexramo]

Thanks guys for taking a look, I just started reading my chapter on arrays in VBA for dummies, so I'll be figuring it out soon.

Just wanted to get a little visual help, as I learn better from seeing the code and watching it work.

Thanks anyways.

 

[xlbo]

Typed untested as an example only - as others have said, this is really not a situation where an array is especially helpful

Dim testArr as Variant, colAArr as variant, colGArr as variant, colIArr as variant

testArr = Range("D1:D1000")
colAArr = Range("A1:A1000")
ColGArr = Range("G1:G1000")
ColIArr = Range("I1:I1000")

For i = 0 to ubound(testArr)
  if testArr(i) = "Y" or testArr(i) = "YES" then
     with sheets("Sheet2")
         .cells(i+1,1).value = colAArr(i)
         .cells(i+1,2).value = colGArr(i)
         .cells(i+1,3).value = colIArr(i)
     end with
  end if
next i

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question