+ Reply to Thread
Results 1 to 4 of 4

VBA Editing Problem

Hybrid View

  1. #1
    Registered User
    Join Date
    03-12-2006
    Posts
    20

    Talking VBA Editing Problem

    I have this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Const WS_Range As String = "b9:c9"
    Dim iColumn As Integer
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    On Error Resume Next
        If Not Intersect(Target, Me.Range(WS_Range)) Is Nothing Then
            iColumn = Target.Column
            If iColumn = 3 Then
                Range("b9").Value = Range("c9").Value / Range("b13").Value
            Else: Range("c9").Value = Range("b9").Value * Range("b13").Value
                End If
            End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    On Error GoTo 0
    End Sub
    It is a simple dynamic conversion tool. Whereas in Excel, we usually have one input cell and one output cell. Through this code, both input cells are also output cells. I.E. Converting Dollar to Peso or Peso to Dollar, Meter to Inches or Inches to Meter....

    Problem is, this code only specifies two specific cells, namely b9 and c9 with b13 as the conversion factor. How can I expand the code to include multiple pairs of cells. I.E. A1:B1, C10:C11,etc by 1) Using the same 'conversion factor' cell (b13) and 2) Using different conversion factors located in different cells on the worksheet.

    Many thanks,

    lex

  2. #2
    Forum Contributor
    Join Date
    07-05-2007
    Location
    Lexington, MA
    Posts
    302
    Store the locations of conversion locations (B9, C9) and the related rate locations (B13) in a string array. Loop through the array to identify changed cells of interest and get the rate from the associated location.
    For further efficiency, this array can be declared globally and filled with data on the Worksheet_Open event.

    Dim cvts(1 To 3, 1 To 20) As String 'Room for 20 conversion pairs
    cvts(1, 1) = "B9"  'First pair location
    cvts(2, 1) = "C9"  'Second pair location
    cvts(3, 1) = "B13" 'Location of rate for this pair
    '... and so on for the other conversion pairs and rates
    FrankBoston is the pen name for Andrew Garland, Lexington MA

  3. #3
    Registered User
    Join Date
    03-12-2006
    Posts
    20
    Quote Originally Posted by FrankBoston
    Store the locations of conversion locations (B9, C9) and the related rate locations (B13) in a string array. Loop through the array to identify changed cells of interest and get the rate from the associated location.
    For further efficiency, this array can be declared globally and filled with data on the Worksheet_Open event.

    Dim cvts(1 To 3, 1 To 20) As String 'Room for 20 conversion pairs
    cvts(1, 1) = "B9"  'First pair location
    cvts(2, 1) = "C9"  'Second pair location
    cvts(3, 1) = "B13" 'Location of rate for this pair
    '... and so on for the other conversion pairs and rates
    Thanks for the reply FrankBoston.

    However, I'm not really that much knowledgeable in VBA. Do I jut copy this code and paste it?

    I plan on doing several pairs of cells to refer to the same conversion factor (b13). How do I do it?

    Many thanks,

    lex

  4. #4
    Forum Contributor
    Join Date
    07-05-2007
    Location
    Lexington, MA
    Posts
    302
    You would need to upload your zipped workbook for more detailed advice.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1