Cant Fill Array 1

[vba317]

I have created the following array:

Dim LR As Long
Dim Fib1 As Double
Dim myarray As Variant
Dim H2 as Double

LR = ActiveSheet.UsedRange.Rows.Count

myarray = Range("G2:G & LR").Value

if H2 >0 than
'Looping structure to look at array.
 For Fib1 = 1 To UBound(myarray)
 MsgBox myarray(Fib1, 1)

Next

Next

I get the error myarray is empty

What I'm trying to do is look at all the data in column G

Some cells in this range are blank
if data exists assign each value to the array H2
H2(1) would be first value
H2(2) would be second value so on and so on

Any help would be greatly appreciated

 

[SkipVought]

I don't know what is in your ranges, but this works...

    Dim i As Long, j As Long
    Dim myarray As Variant
    
    myarray = ActiveSheet.UsedRange.Value
    
    For i = 1 To UBound(myarray, 1)
        For j = 1 To UBound(myarray, 2)
            Debug.Print myarray(i, j)
        Next
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

Skip's method works flawlessly and displays myarray's "values only" in the "immediate window".

An alternate method to see whats in myarray is to right click on myarray and click "add watch" which adds the array to the "watch window" where you can see it's values as the macro is being processed.

With Skips method you only need to run the macro to see all your array elements from first to last in the immediate window.

With the "watch window" method you will need to Step thru the macro using the F8 key which allows to to process the macro one step at a time and view the variables thru each step.

The watch window compresses the array and you will need to click the + signs to open each dimension to view the individual elements.

Forgive me if I've made a simple thing too complicated but I find that most people can fix many of their own problems if they give give "add watch" a try.

Oh BTW Skip is much much better at this stuff than me !!

sam

 

[vba317]

Skip thank you . I do want to start by admitting that this is my first array. I can see that this code did create an array for all the info on my worksheet. i represents the rows and j represents all the columns. What I need to do is get the data from my G column and H column. There are blank cells in each column. What I need to do is subtract the first cell that has data in the G column from the first cell that has data in the H column. and than putting that value in the I column. Is there a way of doing this?

 

[Andrzejek]

Try this little fix in your line [tt]myarray = Range("G2:G" & LR).Value[/tt]:

Dim LR As Long
Dim Fib1 As Double
Dim myarray As Variant
Dim H2 as Double

LR = ActiveSheet.UsedRange.Rows.Count

myarray = Range("G2:G[blue][b]"[/b][/blue] & LR).Value

if H2 >0 than
    'Looping structure to look at array.
    For Fib1 = 1 To UBound(myarray)
    MsgBox myarray(Fib1, 1)

Next

Next

Have fun.

---- Andy

 

[SkipVought]

Assuming that column G has values in each cell within the UsedRange...

Dim Fib1 As LONG
Dim myarray As Variant
Dim H2 as Double

myarray = Range([G2], [G2].end(xldown)).Value
[b]
if[/b] H2 >0 th[b][red]E[/red][/b]n
    'Looping structure to look at array.
    For Fib1 = 1 To UBound(myarray)
       MsgBox myarray(Fib1, 1)

    Next
[b][red]
End If[/red][/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

fussy fussy fussy

Gerry

 

[SkipVought]

The OP is from the Granite State.

Us suthen red necks most al's say...

If...Than...Alse...And

nowutimeen Vern? ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

You crack me up Loon Star state boy. Ooops...sorry I really did mean Lone.........

Gerry

 

[vba317]

Trying to understand the code that was provided I am now getting a message box so I am hoping that I can get some help to the next step. Here is my current code I tried to remove the Msgbox command but I got an error. How can I reword MsgBox myarray(Fib1, 1) so the myarray command takes the array.

Dim Fib1 As Double
Dim myarray As Varian

                                
Dim R2 As Long
                                
For R2 = 2 To LR
myarray = Range([G2], [G2].End(xlDown)).Value
                                
High = Range("G" & R2)
If High > 0 Then
'Looping structure to look at array.
For Fib1 = 1 To UBound(myarray)
MsgBox myarray(Fib1, 1)
Next
 End If
                                
Next R2

 

[SkipVought]

You ought to use the Option Explicit statement at the beginning of your module, ABOVE YOUR PROCEDURES. You have misspelled Variant.

What do you mean by, "so the myarray command takes the array."



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MiggyD]

Didn't your instructor emphasize the necessity of "Commenting Your Code"? Skip did...

if H2 >0 then

    [i]'Looping structure to [u]look at[/u] array.[/i]

    For Fib1 = 1 To UBound(myarray)
       MsgBox myarray(Fib1, 1)
    Next

End If

See the "LOOK AT" in the REM'd line in the code?

The FOR/NEXT code is showing you what is already in the array.

So, it stands to reason that if the message box is Empty then there's nothing in the array. The opposit will also be true: if the message box shows you Something then you can safely presume the array is NOT Empty.

So, once you validate (to yourself) that the code is running fine (at least up to that point) then you can remove the FOR/NEXT section of code and continue on. OR! just REM them out incase you need to review it later.

--Miggyd

My mind not only wanders, it sometimes leaves completely.

 

[vba317]

I have never had an instructor. That's why I joined this website because I have found everyone so helpful, that's also why I continue to ask for advice. I will also admit that I am starting to begin to understand programming principles, thanks to everyone's help.

Skip I tried to add option explicit and got a compile error invalid inside procedure. The 5th cell had a value in it. So R2 = 5 and High = 394.69. In my locals window I see myArray(4,1) is 394.69, the messagebox is still empty 4 times than the 5th time the message appears 394.69.

I kept on stepping, the 9th cell has a value of 403.13
. I stepped 15 times and the 9th value did not get read into the array. Any sugestions?

 

[SkipVought]

Option Explicit does not go IN YOUR CODE.

As I stated, "You ought to use the Option Explicit statement at the beginning of your module, ABOVE YOUR PROCEDURES."

THIS IS THE FIRST LINE OF YOUR MODULE

Option Explicit  'ALWAYS use this statement in EVERY MODULE

'Now your first procedure

Sub YourFirstProcedure()
  'your procedure code in here

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MiggyD]

So, your example data looks something like this?

[tt]
__|__F__|__G__|__H__|__I__|
4_|_____|_____|_____|_____|
5_|_____|_394_|_____|_____|
6_|_____|_____|_____|_____|
7_|_____|_____|_____|_____|
8_|_____|_____|_____|_____|
9_|_____|_____|_403_|_____| <-so a NINE would show in I?
10|_____|_____|_____|_____|
11|_____|_____|_____|_____|
[/tt]

and

...There are blank cells in each column. What I need to do is subtract the first cell that has data in the G column from the first cell that has data in the H column. and than putting that value in the I column.

Am I understanding this correctly? Will the "data" be dynamic? Will it change/move around? If there's something in Col. G will there be nothing in the Col. H immediatly next to it? and vice versa?

Is there any more to the pseudo code I quoted above? what if G5 and G6 have numbers? do you record the larger one and keep going until it Col. H has a number and then subtract the difference?

--MiggyD

My mind not only wanders, it sometimes leaves completely.

 

[SkipVought]

assuming that MiggyD's example is correct, then you must refer to my post of 6 Jun 10 17:50, or you will NEVER "see" the data in more than the first column in your array.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

Miggy

You must have a lot of time on your hands. That's the prettiest text-style spreadsheet I've ever seen.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[vba317]

Thank you MiddyD for your extra effort. Currently the code that creates the H and the G column is as follows:

For R1 = 4 To LR

'Min Value Calculation
'A leg Low location
LowM2 = (Range("D" & R1) - Range("D" & R1 - 2))
LowM1 = (Range("D" & R1) - Range("D" & R1 - 1))
LowP1 = Range("D" & R1 + 1) - Range("D" & R1)
LowP2 = Range("D" & R1 + 2) - Range("D" & R1)

'Max Value Calculation
'B leg High location
HighM2 = (Range("C" & R1) - Range("C" & R1 - 2))
HighM1 = (Range("C" & R1) - Range("C" & R1 - 1))
HighP1 = Range("C" & R1 + 1) - Range("C" & R1)
HighP2 = Range("C" & R1 + 2) - Range("C" & R1)

'A leg Low header
Range("H1").Value = "Low"
                                    
If LowM2 < -0.15 And LowM1 < 0.08 And LowP1 > 0.05 And LowP2 > -0.1 Then Range("H" & R1).Value = (Range("D" & R1))

If LowM2 < -0.15 And LowM1 < 0.08 And LowP1 > 0.05 And LowP2 > -0.1 Then Range("D" & R1).Interior.Color = vbRed
                                                                        
'B leg High header
Range("G1").Value = "High"
                                    
If HighM2 >= -0.2 And HighM1 >= -0.05 And HighP1 < -0.2 And HighP2 < -0.05 Then Range("G" & R1).Value = (Range("C" & R1))

If HighM2 >= -0.2 And HighM1 >= -0.05 And HighP1 < -0.2 And HighP2 < -0.05 Then Range("C" & R1).Interior.Color = vbGreen

Next R1

This is the code that creates G and H columns. What my goal for the last 6 months has been an easy way of subtracting the first occurance of an value in the G column from the first occurance of a value in the H column and multiplying and multipliying that value by .236 and putting that value in the I column. I want to do more with this number but if I could get the code to do this that I can figure out how to end up with the following:

Retracement High Low Diff
0.000 0.000 0.000
0.236 0.382 0.500 0.618 0.786
0.000 0.000 0.000 0.000 0.000

Hope this helps

 

[MiggyD]

I'm not sure what data you're using but here's my take on what you've already done:

Option Explicit

Sub DoSomething()

Dim LR As Long
Dim R1 As Integer
Dim LowM2, LowM1, LowP1, LowP2
Dim HighM2, HighM1, HighP1, HighP2
Dim MarkedRed   ' Tracker for column H
Dim MarkedGreen ' Tracker for column G

' Setup worksheet
Range("G1").Value = "High"          'B leg High header
Range("H1").Value = "Low"           'A leg Low header
Range("I1").Value = "Diff"          'b-a or a-b difference header
Range("J1").Value = "Fini (w/.236)" 'Finial output (((a-b)+b)*.236)

' Ini Variables & Fill Static Vars.
MarkedRed = 0: MarkedGreen = 0
LR = ActiveSheet.UsedRange.Rows.Count

' ** START - Main Routine **
For R1 = 4 To LR
    '
    'Min Value Calculation // A leg Low location
    '
    LowM2 = (Range("D" & R1) - Range("D" & R1 - 2))
    LowM1 = (Range("D" & R1) - Range("D" & R1 - 1))
    LowP1 = Range("D" & R1 + 1) - Range("D" & R1)
    LowP2 = Range("D" & R1 + 2) - Range("D" & R1)
    '
    'Max Value Calculation // B leg High location
    '
    HighM2 = (Range("C" & R1) - Range("C" & R1 - 2))
    HighM1 = (Range("C" & R1) - Range("C" & R1 - 1))
    HighP1 = Range("C" & R1 + 1) - Range("C" & R1)
    HighP2 = Range("C" & R1 + 2) - Range("C" & R1)
    '
    ' Leg LOW validator
    '
    If LowM2 < -0.15 And LowM1 < 0.08 And LowP1 > 0.05 And LowP2 > -0.1 Then
        Range("H" & R1).Value = (Range("D" & R1))
        Range("D" & R1).Interior.Color = vbRed
        '
        ' record cell value for later calculations
        MarkedRed = Range("D" & R1)
        '
        ' check other tracking marker to see if
        ' calcuations are to be done at this time
        If MarkedGreen <> 0 Then
            '
            ' Put answer in Column I.
            Range("I" & R1).Value = (MarkedRed - MarkedGreen) + MarkedRed
            Range("J" & R1).Value = (((MarkedRed - MarkedGreen) + MarkedRed) * 0.236)
            '
            ' RESET trackers for next group of numbers (if any)
            MarkedRed = 0: MarkedGreen = 0
        ElseIf MarkedGreen = 0 Then
            Range("I" & R1).Value = "Cal-Err"
        End If
    End If
    '
    ' Leg HIGH validator
    '
    If HighM2 >= -0.2 And HighM1 >= -0.05 And HighP1 < -0.2 And HighP2 < -0.05 Then
        Range("G" & R1).Value = (Range("C" & R1))
        Range("C" & R1).Interior.Color = vbGreen
        '
        ' record cell value for later calculations
        MarkedGreen = Range("C" & R1)
        '
        ' check other tracking marker to see if
        ' calcuations are to be done at this time
        If MarkedRed <> 0 Then
            '
            ' Put answer in Column I.
            Range("I" & R1).Value = (MarkedGreen - MarkedRed) + MarkedGreen
            Range("J" & R1).Value = (((MarkedGreen - MarkedRed) + MarkedGreen) * 0.236)
            '
            ' RESET trackers for next group of numbers (if any)
            MarkedRed = 0: MarkedGreen = 0
        ElseIf MarkedRed = 0 Then
            Range("I" & R1).Value = "Cal-Err"
        End If
    End If
Next R1
'
' ** END - Main Routine**
'
MsgBox "Differential Calculations Are Done ", vbOKOnly
End Sub

HTH
--MiggD

My mind not only wanders, it sometimes leaves completely.

 

[vba317]

MiggyD thanks for your help!! Your code solves 90% of what I need to do. Thanks!!!