+ Reply to Thread
Results 1 to 17 of 17

New to writing macros

Hybrid View

  1. 08-31-2007, 12:02 AM #1
    ynnod
    ynnod is offline
    Registered User
    Join Date
    11-01-2006
    Posts
    24

    New to writing macros- Please help.

    Hello,

    I need to write a macro that compares the numerical value of 2 cells and give me a result as described below:

    All the numbers would be either whole numbers or whole numbers + 0.5

    Example 1:

    The two values are both whole numbers then no action is taken.

    Cell A = 5
    Cell B = 5

    or

    Cell A = 7
    Cell B = 5

    Result = No action taken

    Example 2

    The two values are both equal half numbers then 0.5 is subtracted from one number and 0.5 is added to the other number to make them both whole numbers.

    Cell A = 5.5
    Cell B = 5.5

    Result = 0.5 is subtracted from Cell A to make it 5, 0.5 is added to Cell B to make it 6.

    Example 3

    The two values are unequal half numbers then 0.5 is subtracted from the number with the lower value and 0.5 is added to the number with the higher value to make them both whole numbers.

    Cell A = 5.5
    Cell B = 8.5

    Result = 0.5 is added to Cell A to make it 6, 0.5 is subtracted from Cell B to make it 8.

    I hope this makes sense.As I mentioned, I'm just learning to write macros so any help is GREATLY appreciated.

    ~Donny
    Last edited by ynnod; 08-31-2007 at 11:00 PM.
    Reply With QuoteRegister To Reply
  2. 08-31-2007, 12:40 AM #2
    Paul
    Paul is offline
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887
    Hi Donny,

    I think this code should do what you're asking:
    Sub checkValues()
    Dim Value1 As Single, Value2 As Single
    Value1 = Sheets("Sheet1").Range("A1").Value
    Value2 = Sheets("Sheet1").Range("B1").Value
    
    If (Value1 - Int(Value1) = 0) And (Value2 - Int(Value2) = 0) Then
        Exit Sub
    ElseIf (Value1 = Value2) And ((Value1 - Int(Value1) <> 0) And (Value2 - Int(Value2) <> 0)) Then
        Range("A1").Value = Value1 - 0.5
        Range("B1").Value = Value2 + 0.5
    ElseIf (Value1 <> Value2) And ((Value1 - Int(Value1) <> 0) And (Value2 - Int(Value2) <> 0)) Then
        If Value1 < Value2 Then
            Range("A1").Value = Value1 - 0.5
            Range("B1").Value = Value2 + 0.5
        Else
            Range("A1").Value = Value1 + 0.5
            Range("B1").Value = Value2 - 0.5
        End If
    Else
        MsgBox "Something ain't right here.."
    End If
End Sub
    Substitute A1 and B1 for your two cells. You can have this code run from a command button or a shortcut key (or by manually running through the Macros screen).
    - Paul

    Click here to read the Forum Rules

    Reply With QuoteRegister To Reply
  3. 08-31-2007, 01:23 AM #3
    ynnod
    ynnod is offline
    Registered User
    Join Date
    11-01-2006
    Posts
    24
    Thank you so much! I believe this will work.
    Reply With QuoteRegister To Reply
  4. 08-31-2007, 10:56 PM #4
    ynnod
    ynnod is offline
    Registered User
    Join Date
    11-01-2006
    Posts
    24
    Ok, so this worked with just two values. I am now trying to get it to work with 48 adjacent cells in 1 column (24 sets of 2 values to compare), and the macro isn't doing anything when I run it. Below is what I've done so far. I've defined all 48 variables (correctly I think) and put in code to process the first two sets of two numbers.

    Can anybody see what's wrong? Please help if you can.

    ~Donny

        Dim Value1 As Single, Value2 As Single, Value3 As Single, Value4 As Single, Value5 As Single, Value6 As Single, Value7 As Single, Value8 As Single, Value9 As Single, Value10 As Single, Value11 As Single, Value12 As Single, Value13 As Single, Value14 As Single, Value15 As Single, Value16 As Single, Value17 As Single, Value18 As Single, Value19 As Single, Value20 As Single, Value21 As Single, Value22 As Single, Value23 As Single, Value24 As Single, Value25 As Single, Value26 As Single, Value27 As Single, Value28 As Single, Value29 As Single, Value30 As Single, Value31 As Single, Value32 As Single, Value33 As Single, Value34 As Single, Value35 As Single, Value36 As Single, Value37 As Single, Value38 As Single, Value39 As Single, Value40 As Single, Value41 As Single, Value42 As Single, Value43 As Single, Value44 As Single, Value45 As Single, Value46 As Single, Value47 As Single, Value48 As Single
    Value1 = Sheets("MainPage").Range("D60").Value
    Value2 = Sheets("MainPage").Range("D61").Value
    Value3 = Sheets("MainPage").Range("D62").Value
    Value4 = Sheets("MainPage").Range("D63").Value
    Value5 = Sheets("MainPage").Range("D64").Value
    Value6 = Sheets("MainPage").Range("D65").Value
    Value7 = Sheets("MainPage").Range("D66").Value
    Value8 = Sheets("MainPage").Range("D67").Value
    Value9 = Sheets("MainPage").Range("D68").Value
    Value10 = Sheets("MainPage").Range("D69").Value
    Value11 = Sheets("MainPage").Range("D70").Value
    Value12 = Sheets("MainPage").Range("D71").Value
    Value13 = Sheets("MainPage").Range("D72").Value
    Value14 = Sheets("MainPage").Range("D73").Value
    Value15 = Sheets("MainPage").Range("D74").Value
    Value16 = Sheets("MainPage").Range("D75").Value
    Value17 = Sheets("MainPage").Range("D76").Value
    Value18 = Sheets("MainPage").Range("D77").Value
    Value19 = Sheets("MainPage").Range("D78").Value
    Value20 = Sheets("MainPage").Range("D79").Value
    Value21 = Sheets("MainPage").Range("D80").Value
    Value22 = Sheets("MainPage").Range("D81").Value
    Value23 = Sheets("MainPage").Range("D82").Value
    Value24 = Sheets("MainPage").Range("D83").Value
    Value25 = Sheets("MainPage").Range("D84").Value
    Value26 = Sheets("MainPage").Range("D85").Value
    Value27 = Sheets("MainPage").Range("D86").Value
    Value28 = Sheets("MainPage").Range("D87").Value
    Value29 = Sheets("MainPage").Range("D88").Value
    Value30 = Sheets("MainPage").Range("D89").Value
    Value31 = Sheets("MainPage").Range("D90").Value
    Value32 = Sheets("MainPage").Range("D91").Value
    Value33 = Sheets("MainPage").Range("D92").Value
    Value34 = Sheets("MainPage").Range("D93").Value
    Value35 = Sheets("MainPage").Range("D94").Value
    Value36 = Sheets("MainPage").Range("D95").Value
    Value37 = Sheets("MainPage").Range("D96").Value
    Value38 = Sheets("MainPage").Range("D97").Value
    Value39 = Sheets("MainPage").Range("D98").Value
    Value40 = Sheets("MainPage").Range("D99").Value
    Value41 = Sheets("MainPage").Range("D100").Value
    Value42 = Sheets("MainPage").Range("D101").Value
    Value43 = Sheets("MainPage").Range("D102").Value
    Value44 = Sheets("MainPage").Range("D103").Value
    Value45 = Sheets("MainPage").Range("D104").Value
    Value46 = Sheets("MainPage").Range("D105").Value
    Value47 = Sheets("MainPage").Range("D106").Value
    Value48 = Sheets("MainPage").Range("D107").Value
    
    
    If (Value1 - Int(Value1) = 0) And (Value2 - Int(Value2) = 0) Then
        Exit Sub
    ElseIf (Value1 = Value2) And ((Value1 - Int(Value1) <> 0) And (Value2 - Int(Value2) <> 0)) Then
        Range("D60").Value = Value1 - 0.5
        Range("D61").Value = Value2 + 0.5
    ElseIf (Value1 <> Value2) And ((Value1 - Int(Value1) <> 0) And (Value2 - Int(Value2) <> 0)) Then
        If Value1 < Value2 Then
            Range("D60").Value = Value1 + 0.5
            Range("D61").Value = Value2 - 0.5
        Else
            Range("D60").Value = Value1 - 0.5
            Range("D61").Value = Value2 + 0.5
        End If
    End If
    
    If (Value3 - Int(Value3) = 0) And (Value4 - Int(Value4) = 0) Then
        Exit Sub
    ElseIf (Value3 = Value4) And ((Value3 - Int(Value3) <> 0) And (Value4 - Int(Value4) <> 0)) Then
        Range("D62").Value = Value3 - 0.5
        Range("D63").Value = Value4 + 0.5
    ElseIf (Value3 <> Value4) And ((Value3 - Int(Value3) <> 0) And (Value4 - Int(Value4) <> 0)) Then
        If Value3 < Value3 Then
            Range("D62").Value = Value3 + 0.5
            Range("D63").Value = Value4 - 0.5
        Else
            Range("D62").Value = Value3 - 0.5
            Range("D63").Value = Value4 + 0.5
        End If
    End If
End Sub
    Reply With QuoteRegister To Reply
  5. 08-31-2007, 11:42 PM #5
    Paul
    Paul is offline
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887
    It's probably not working because your first test (value1 and value2) are both whole numbers - which tells the macro to 'Exit Sub', so no more code runs.

    Had I known you were going to compare 48 cells in a vertical column I'd have written the code differently, using a loop. I'll see what I can put together and get back to you.
    Reply With QuoteRegister To Reply
  6. 08-31-2007, 11:52 PM #6
    ynnod
    ynnod is offline
    Registered User
    Join Date
    11-01-2006
    Posts
    24
    Thanks very much Paul. If you also know a way to define all those variable (I hope I am using the right terminology) that would be great as well. It seems like there should probably a easier way.......

    ~Donny
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1