• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA macro that changes two cell values based on one of the cell's inputs

Sorry to repost, this is my first time using this site; post was initially in "Ask an Excel Question" forum but obviously "VBA Macros" seems like a more logical place. If I figure out how to delete the post in the other forum, I'll be sure to do that.

Hi there, looking for some VBA help with a macro I want to write that does the following: suppose B1 has some number in it and I type a number into A1; if A1 is greater than or equal to 100*(B1+1), then the value in B1 goes up by 1 and the value in A1 goes down by 100*B1 (the new value in B1); otherwise, the program does nothing; and the program loops as necessary.

For example, suppose A1 = 1000 and B1 = 2; since 1000 >= 100*(2 + 1), B1 goes up to 3 and A1 goes down to 700. Since we now have A1 = 700 and B1 = 3, the program would run again because 700 >= 100*(3 + 1), so B1 goes up to 4 and A1 goes down to 300. Now that A1 = 300 and B1 = 4, the values don't satisfy the conditions to do anything so the values stay put until I change the value of A1.

This is my first time trying to use VBA (most everything I've done with Excel can be handled by Boolean operators) so I'm trash with VBA syntax; providing the code would be awesome, providing explanation about what the code is doing would be next-level amazing so I can try and understand how the VBA syntax works.
 
Last edited:
Try putting the following into the VBA code for ThisWorkbook

>>> use code - tags <<<
Code:
Option Explicit
Dim bBusy As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rgA1 As Range, rgB1 As Range
    Dim lA1 As Long, lB1 As Long
    If bBusy Then
        Exit Sub
    End If
    Set rgA1 = Sheets("Test Sheet").Cells(1, 1)
    If Sh.Name <> "Test Sheet" Then
        Exit Sub
    End If
    If Application.Intersect(Target, rgA1) Is Nothing Then
        Exit Sub
    End If
    bBusy = True
    Set rgB1 = Sheets("Test Sheet").Cells(1, 2)
    lA1 = Val(rgA1)
    lB1 = Val(rgB1)
    Do While lA1 >= 100 * (lB1 + 1)
        lB1 = lB1 + 1
        lA1 = lA1 - 100 * lB1
    Loop
    rgA1 = lA1
    rgB1 = lB1
    bBusy = False
End Sub
 
Last edited by a moderator:
Thank you for your help, I appreciate it! However, when I copy/paste the code into the VBA editor and go back to the spreadsheet, nothing happens, none of the values update. Am I not correctly inputting the code? All I do is right-click on the sheet tab at the bottom, click "View Code", then paste the code you posted above. Is there something else I need to do? Is there some additional code I need to make the macro run automatically whenever the value in A1satisfies the requirements?
 
A couple of possibilities:
- could you check that the name of your worksheet is "Test Sheet" or else change the code to the reflect the name of your sheet
- the code is triggered by a change in your workbook. Can you try updating the value of A1 to see if that works.

I attach the workbook I tested this on. You will need to enable macros to get this to work.
 

Attachments

  • Book1.xlsm
    15.6 KB · Views: 3
Thank you so much! The first item you noted was a problem I was having, and I also apparently wasn't saving the macro in the right place. Once I figured out how to do that, I was able to get it to work on a spreadsheet I made myself. Thanks again, I really appreciate it!
 
Back
Top