1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel & VBA

Discussion in 'Business Applications' started by AMorrison0903, Apr 11, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. AMorrison0903

    AMorrison0903 Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    79
    Hello everyone...

    I am a network engineer, mcse, however, I was asked to do some programming (which i havent done since high school) anyways, i will cut to chase...

    in short, i need excel to look at columnA with columnB and corresponding row... if columnA, row 1 is =< columnB, row 1 then that is the answer, if not, then move to the next row... if columnA row 2 is =< columnB, row 2, then that is the answer, if not move to the next row and so forth.

    i have the rest of the program done it is just this last part.

    I have narrowed it down to a for loop as what to use and i would assume if then statements... unfortunately i cant figure out how to make it work... it has been years since i have done that so if anyone can help, i will be greatly indebted.

    thanks, andrew
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I am still confused by what the macro should be doing. Do you want to highlight rows that meet or do not meet the condition? Do you want a count of how many rows meet or do not meet the condition? I'm not trying to be a pain but your explanation of what you want is not very clear. Please explain the "big" picture and we'll provide you with a solution. What are you ultimately trying to accomplish? What type of data is being tracked in this spreadsheet and how will it be used?

    It is very possible that you don't need a macro for what you are trying to achieve. You can easily compare column A and column B using a formula in a "helper" column and then copy the formula down for all rows. You can then filter, count, or sort results based on the values in the helper column. Not knowing what the desired result is makes it hard for us to help.

    Regards,
    Rollin
     
  3. AMorrison0903

    AMorrison0903 Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    79
    my post made sense to me, but i suppose thats because i know the big picture =)

    ill try to explain... and thank you for the patience!

    this program is company wide and the basis of it is provide an answer with the inputed variables, as most solutions do that...

    after all variables are in place, the program creates 2 columns of "possibilities"... column A and column B for example. What I need it to do compare both columns in the same row (i.e. A1 with B1, or A33 with B33) and find when column A is less than or equal to column B (i.e. A1 =< B1) and then output the positive match as the solution, there will only be ONE positive match, and then obviously only one solution.

    Yes, you are correct, a macro may not be suitable, i am not completely sure, that is why i am asking for help and direction.

    What i have done so far is set up the "input" cells for employees to key in the variables and for the program to output that to two separate columns.... column A and column B...

    i hope that makes sense, please let me know if i need to further clarify...

    I thank you for your, and everyone elses, help!

    Andrew
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Will there ever be any blank rows of data in column A or column B ?? What row will the first record start in?

    Regards,
    Rollin
     
  5. AMorrison0903

    AMorrison0903 Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    79
    there will not be any blank rows... the data will start on row 1.

    also, i failed to mention that the solution will be displayed on a separate sheet within the same workbook...
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    There are lots of different ways to code this. The code below assumes that there are no blank lines of data in column A or B. Just change the value in the second line to reflect the correct starting row.

    Code:
    Sub FindAnswer()
    
    StartRow = 1
    
    For i = StartRow To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    If Range("A" & i).Value <= Range("B" & i).Value Then
    MsgBox ("Answer Found on Row " & i)
    
    'You can also output your answer to another sheet (Sheet 2 cell A1) using the line below.
    Sheets("Sheet2").Range("A1").Value = Range("A" & i).Value
    
    Exit Sub
    End If
    Next i
    
    MsgBox ("Not Found")
    End Sub
    
    Regards,
    Rollin
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Try using code to create formulas in a temporary* helper column:

    Sub test()
    Range("A1", Range("A" & Rows.Count).End(xlUp)).Offset(, 2).FormulaR1C1 = "=IF(RC[-2]<=RC[-1],0,1)"
    x = Application.Match(0, Range("C:C"), 0)
    Range("C:C").ClearContents
    MsgBox "Solution is in row " & x & "."
    End Sub


    * in this case column C
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    :mad:




    :D
     
  9. AMorrison0903

    AMorrison0903 Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    79
    Rollin... Thank you so much! Who do i write the check out to? :D

    that is exactly what i needed!

    thank you for your help too bomb!

    I have spent the last week going through vba books, programming excel with vba and excel for dummies trying to find the correct solution... didnt know to look for.
     
  10. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Wow! Americans spell cheque "check" - that so weird!! - sorry that's a new linguistic discovery for me!
     
  11. AMorrison0903

    AMorrison0903 Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    79
    hello guys,

    I have another issue in reference to this project. There will always be more than 1 possible solution and i need to pick the solution of the highest value...

    does this make sense?

    how do i do this?
     
  12. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try the code below. Once again this code assumes that the first record starts on row 1. Change the first line of code to match the correct starting row number if your data begins in a row other than 1.

    Code:
    Sub FindAnswer()
    
    StartRow = 1
    vFoundValue = 0
    vFoundRow = 0
    
    For i = StartRow To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    If Range("A" & i).Value <= Range("B" & i).Value And Range("A" & i).Value > vFoundValue Then
    vFoundValue = Range("A" & i).Value
    vFoundRow = i
    End If
    
    Next i
    
    If vFoundValue > 0 Then
    
    MsgBox ("Highest Answer: " & vFoundValue & vbCrLf & "Found on Row: " & vFoundRow)
    
    'Output answer to Sheet2 cell A1
    Sheets("Sheet2").Range("A1").Value = vFoundValue
    
    Else
    
    MsgBox ("Answer Not Found")
    
    End If
    
    End Sub
    Regards,
    Rollin
     
  13. AMorrison0903

    AMorrison0903 Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    79
    Awesome work once again.

    I had to edit a few things, but it worked great! thank you sir!

    so i dont have to bug you (or at least not as much), any good suggestions for books for reference for vba with excel? i know there is alot to learn and know... just wondering.

    thank you sir!
     
  14. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    My personal experience is that books are not much help. I find that books are good for learning the basics (variables, arrays, loops, conditional statements, etc.) but most "real world" situations that require special logic can not be easily looked up in books. I always feel that online resources are the best. Using Google and posting in forums like this are probably the easiest way to learn along with playing with the macro recorder and reading and trying to interpret the code that is generated. Feel free to ask questions here as often as you like. Many of us are addicted to these forums and we thrive on the satisfaction from helping others.

    Regards,
    Rollin
     
  15. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    As a reminder please mark you second new thread as "Solved" so that others know that you have found a solution. One more thing I almost forgot to tell you....Welcome to TSG Forums!!

    Regards,
    Rollin
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/702797

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice