Not sure if the goes here but Excel VB help needed

Page 1 of 1

8 Replies - 596 Views - Last Post: 11 January 2013 - 11:07 AMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'https://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=306203&amp;s=6ca3f0759b98c73781ed4116b86811bc&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

Reputation: 0
• Posts: 20
• Joined: 21-February 11

Not sure if the goes here but Excel VB help needed

Posted 08 January 2013 - 08:15 PM

So first off, I have a little programming experience with Javascript, PHP, and other web based languages so I figured I might jump into VB while doing my spread sheets. I seem to be stuck though and need some help. So here's what I need help with, I have a spreadsheet that has A-I, 1-32. I was wanting to get I1-32 to take the value of of row C of the same column, match the data there with data from an array then multiply it by the value of row H of the same column. I attempted to do it and failed but I probably have the concept wrong, here is what I had:

```Private Sub Worksheet_Calculate(ByVal Target As Range)
Dim x(1 To 9) As Integer
x(1) = "1"
x(2) = "2"
x(3) = "4"
x(4) = "8"
x(5) = "16"
x(6) = "32"
x(7) = "64"
x(8) = "128"
x(9) = "256"
Dim y As Integer
Dim c As Integer
Dim p As Integer
Dim t As Integer
c = Target.Column
y = c & Column.Value
p = H & Column.Value
t = x(y) * p
While (H! = Null)
Range("I*").Value = t
Loop
End Sub

```

Any help is greatly appreciated.

Is This A Good Question/Topic? 0

Replies To: Not sure if the goes here but Excel VB help needed

#2 _HAWK_

• Master(Of Foo)

Reputation: 1162
• Posts: 4,444
• Joined: 02-July 08

Re: Not sure if the goes here but Excel VB help needed

Posted 08 January 2013 - 08:48 PM

```While Not H Is Nothing ' if you are looping
```

Or,

```If Not H Is Nothing Then
Range("I*").Value = t
End If
```

What errors are you getting?

This post has been edited by _HAWK_: 08 January 2013 - 08:49 PM

Reputation: 0
• Posts: 20
• Joined: 21-February 11

Re: Not sure if the goes here but Excel VB help needed

Posted 08 January 2013 - 09:32 PM

_HAWK_, on 08 January 2013 - 07:48 PM, said:

```While Not H Is Nothing ' if you are looping
```

Or,

```If Not H Is Nothing Then
Range("I*").Value = t
End If
```

What errors are you getting?

I get "Procedure declaration does not match description of event or procedure having the same name." It highlights the first line, looking over some other code that did something similar I thought that would work. any suggestions?

#4 _HAWK_

• Master(Of Foo)

Reputation: 1162
• Posts: 4,444
• Joined: 02-July 08

Re: Not sure if the goes here but Excel VB help needed

Posted 08 January 2013 - 09:54 PM

Is this VBA or VB.Net in an application?

Reputation: 0
• Posts: 20
• Joined: 21-February 11

Re: Not sure if the goes here but Excel VB help needed

Posted 10 January 2013 - 11:25 AM

_HAWK_, on 08 January 2013 - 08:54 PM, said:

Is this VBA or VB.Net in an application?

VBA, it's in the Excel sheet.

#6 andrewsw

• RequestedRangeNotSatisfiable

Reputation: 6552
• Posts: 26,563
• Joined: 12-December 12

Re: Not sure if the goes here but Excel VB help needed

Posted 10 January 2013 - 11:45 AM

The Calculate event for a worksheet doesn't have any arguments. Cut out your code. Right-click the worksheet tab in Excel and choose View Code. Choose Worksheet from the drop-down at the top-left, and then Calculate from the drop-down on the right. This will create an empty procedure stub for you, like this:

```Private Sub Worksheet_Calculate()

End Sub
```

Paste your code inbetween - and press Tab to indent it!

You have declared your array as Integer but you are storing "strings" in it.

You can't refer to Target within this code - it is not available. (It would be if you chose the event Change - perhaps this is what you intended.)

It is
```Do While Not H Is Nothing
'code here
Loop
```

but you haven't declared or assigned H..

There are a few more mistakes..

.. be careful using the Calculate event if you are modifying data in the sheet. Are you sure you shouldn't just have a button to click on to run your code?

#7 andrewsw

• RequestedRangeNotSatisfiable

Reputation: 6552
• Posts: 26,563
• Joined: 12-December 12

Re: Not sure if the goes here but Excel VB help needed

Posted 10 January 2013 - 12:13 PM

I hope this isn't homework!!!

```Sub RunOnButtonclick()
Dim x(1 To 9) As Integer
Dim y As Variant
Dim i As Integer
Dim rng As Range

Application.ScreenUpdating = False
x(1) = 1
x(2) = 2
x(3) = 4
x(4) = 8
x(5) = 16
x(6) = 32
x(7) = 64
x(8) = 128
x(9) = 256
'or..
For i = 1 To 9
x(i) = 2 ^ (i - 1)
Next i
'.. but this could also be done in directly in the formula

'or..
y = Array(0, 1, 2, 4, 8, 16, 32, 64, 128, 256)

For Each rng In Range("I1:I32")
If IsNumeric(rng.Value) Then
rng.Value = x(Range("C1").Cells(rng.Row).Value) * _
Range("H1").Cells(rng.Row).Value
End If
Next rng

Application.ScreenUpdating = True
End Sub
```

Reputation: 0
• Posts: 20
• Joined: 21-February 11

Re: Not sure if the goes here but Excel VB help needed

Posted 11 January 2013 - 10:35 AM

andrewsw, on 10 January 2013 - 11:13 AM, said:

I hope this isn't homework!!!

```Sub RunOnButtonclick()
Dim x(1 To 9) As Integer
Dim y As Variant
Dim i As Integer
Dim rng As Range

Application.ScreenUpdating = False
x(1) = 1
x(2) = 2
x(3) = 4
x(4) = 8
x(5) = 16
x(6) = 32
x(7) = 64
x(8) = 128
x(9) = 256
'or..
For i = 1 To 9
x(i) = 2 ^ (i - 1)
Next i
'.. but this could also be done in directly in the formula

'or..
y = Array(0, 1, 2, 4, 8, 16, 32, 64, 128, 256)

For Each rng In Range("I1:I32")
If IsNumeric(rng.Value) Then
rng.Value = x(Range("C1").Cells(rng.Row).Value) * _
Range("H1").Cells(rng.Row).Value
End If
Next rng

Application.ScreenUpdating = True
End Sub
```

Not homework, but it's kind of embarrassing, it's for a Facebook game I play. Can the functions really handle arrays? And was I at least kind of close with the concept?

#9 andrewsw

• RequestedRangeNotSatisfiable

Reputation: 6552
• Posts: 26,563
• Joined: 12-December 12

Re: Not sure if the goes here but Excel VB help needed

Posted 11 January 2013 - 11:07 AM

..kinda..

Yes it can handle arrays; just swap y for x. It can even be done as a single Excel array-formula:

{=INDEX({1,2,4,8,16,32,64,128,256},C1:C32)*H1:H32}