Search:

Type: Posts; User: offthelip; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.04 seconds.

  1. Re: An incorrect variable value is being written to a cell

    There is a lot of code there, one thing that I notice is that you start using activecell without ever specifying what cell is active. you activate worksheet but never activate a specific cell.
    Using...
  2. Replies
    14
    Views
    203

    Re: vba for a range and return value

    I just spotted a typo in the code "k" should be "kk"
    try this:


    Sub test()
    lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    inarr = Range(Cells(1, 2), Cells(lastrow, 3))
    outarr = Range(Cells(1,...
  3. Replies
    14
    Views
    203

    Re: vba for a range and return value

    I think I have understood your requirements, if so this should do it:


    lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    inarr = Range(Cells(1, 2), Cells(lastrow, 3))
    outarr = Range(Cells(1, 4),...
  4. Replies
    14
    Views
    203

    Re: vba for a range and return value

    this code is shows you how to do a MATCH in VBA:


    marr = Range("NameManager1")
    farr = Range("Namemanager")
    For kk = LBound(marr, 1) To UBound(marr, 1)
    If marr(kk, 1) = farr Then
    MsgBox...
  5. Replies
    14
    Views
    203

    Re: vba for a range and return value

    Both of your equations are invalid in my version of EXCEL (2007) so I don't know what you are trying to do, so can you explain in words,
    The functionality of the MATCH function can easily be done...
  6. Replies
    14
    Views
    203

    Re: vba for a range and return value

    I forgot to answer your question about using a named range, yes you can use a named range in the code but it will need to ber changed, What columns does your named range cover?
  7. Replies
    14
    Views
    203

    Re: vba for a range and return value

    The reason the code is a bit slow is that everytime VBA writes to the cell A3 it will force a recalculation, the code could be speeded up a bit by writing the column D in one go by using a variant...
  8. Thread: If, and, or

    by offthelip
    Replies
    8
    Views
    124

    Re: If, and, or

    I don't think you need the IF
    =(AND(K3="V",OR(AP3="L", AR3="L"))
    should be adequate
  9. Replies
    14
    Views
    203

    Re: vba for a range and return value

    The code is very simple and should do what you asked, have you checked how "YES" is spelt do you have it all in UPPER CASE?
  10. Replies
    14
    Views
    203

    Re: vba for a range and return value

    I presumed you want the values to be put in A1 for every row in B and C, so try this:


    Sub test()
    lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    inarr = Range(Cells(1, 2), Cells(lastrow, 3))
    ...
  11. Re: Match value from another worksheet, if they match return value from left of matched value

    use the two functions INDEX and MATCH
  12. Replies
    4
    Views
    145

    Re: VBA-Delete all Filtered Rows

    I wouldn't bother about using a filter which can be slow , do it directly like this:


    Sub test()
    lastrow = Cells(Rows.Count, "D").End(xlUp).Row
    inarr = Range(Cells(1, 4), Cells(lastrow, 4))...
  13. Replies
    3
    Views
    49

    Re: Saving a variable into clipboard

    you are welcome; avoiding a problem is always better than solving a problem
  14. Replies
    2
    Views
    99

    Re: alternative to Vlookup with VBA.

    try this code which uses variant arrays and should be very fast. (UNTESTED!!)


    Sheets("Sending List").Select


    Dim Lastrow1, Lastrow2 As Long
    Dim ws1, ws2 As Worksheet
    Dim tempVal, tempVal2...
  15. Replies
    3
    Views
    49

    Re: Saving a variable into clipboard

    If I was designing a system like this I wouldn't use the clipboard for doing the copying at all, I would get the users to copy by clicking on a button that copied what they selected to a variant...
  16. Replies
    5
    Views
    151

    Re: VBA code Help

    What is the error you are getting? also what did you enter in the worksheet and where? and can you post the code the you are using and tell me where you have put it. did it trigger automatically when...
  17. Replies
    5
    Views
    151

    Re: VBA code Help

    This code will automatically put a Company Code inot column B whenever a nmae that matches the list is entered in Column A:
    This code uses the worksheet change event so it must be put in the Sheet...
  18. Replies
    2
    Views
    168

    Re: VBA with condition

    If you want to do it using VBA then this will do it for you:

    Sub test()
    lastrow = Cells(Rows.Count, "I").End(xlUp).Row
    inarr = Range(Cells(1, 8), Cells(lastrow, 9))
    For i = 1 To lastrow
    Yr =...
  19. Replies
    2
    Views
    168

    Re: VBA with condition

    You can do it with an equation:
    =IF(AND(YEAR(I1)=2017,H1>=0.5),"A","D")
  20. Replies
    4
    Views
    165

    Re: Longitude Latitude Question

    If you want a reasonable flat earth type of calculation you can do this easily by using the equivalence between one minute of arc and 1 nautical mile which is about 6076 feet
    To find the difference...
  21. Re: For each loop is working for only first item in a range

    try thiscode:


    Sub test()
    MyInput = Application.InputBox("Enter the item name", "")
    If MyInput = False Or MyInput = "" Then Exit Sub
    MyInput =...
  22. Thread: VBA loops

    by offthelip
    Replies
    2
    Views
    127

    Re: VBA loops

    this code shows you how to generate the file names:

    Sub test()
    colr = Array("Blue", "Red", "Green", "Yellow")
    suit = Array("Clubs", "Diamonds", "Hearts", "Spades")
    For i = 0 To 3
    For j...
  23. Replies
    10
    Views
    188

    Re: Exiting a nested for cycle

    you can put them all in a subroutine and use exit sub, this is what I do for multiple loops. You do need to declare variables at the module level though!
  24. Re: Macro Application.OnTime TimeValue doesnt run all the time

    Your problem is because you only call "scheduler" once ( when workbook open is called) after that you keep calling "collect save" so Copy_master_IV_copy " will never be called, you need to change the...
  25. Re: Macro Application.OnTime TimeValue doesnt run all the time

    Well done you spotted the error which stopped it working!! I did state that it was untested!!
    I am pleased to hear it is all working Ok now!!
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4