8 Replies - 302 Views - Last Post: 11 January 2013 - 11:07 AM Rate Topic: -----

#1 Tassadar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1055
  • View blog
  • Posts: 4,087
  • 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

Was This Post Helpful? 0
  • +
  • -

#3 Tassadar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Post_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?
Was This Post Helpful? 0
  • +
  • -

#4 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1055
  • View blog
  • Posts: 4,087
  • 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?
Was This Post Helpful? 0
  • +
  • -

#5 Tassadar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Post_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.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,869
  • 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?
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,869
  • 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

Was This Post Helpful? 0
  • +
  • -

#8 Tassadar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postandrewsw, 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?
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,869
  • 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}
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1