none
Inputting a value into a Excel spreadsheet active cell via VB programming using indexing RRS feed

  • Question

  • This code works until  until I want to change the value in the active cell.  When it hits the right logic, instead of  changing the active cell value to 0 it resets the index value to 0.

    This code looks very similiar to code you find in the help files except how the index is set up. The problem code is in bold. Any help to solve this mystery would be appreciated.

    'Check for instances of EE hours > 0 and instances of suuplier costs > 0.
    'This will determine whether EE and / or Supplier summaries can be prepared.
    'First initialize flags to 0 then evaluate each row
        Exp_Supplier = 0
        Exp_Labour = 0
       
        Set isect = Application.Intersect(Range("db_Master"), Range("B:B"))
        isect.Select
    
        For Each c In isect ' check for EE > 0
            If c.Value = "EE" And c.Offset(0, 19).Value > 0 Then Exp_Labour = 1: Exit For
        Next
    
        For Each c In isect ' check for Sup > 0
            If c.Value = "Sup" And c.Offset(0, 22).Value > 0 Then Exp_Supplier = 1: Exit For
        Next
          
        For Each c In isect ' check for Document# = ER0
            
            If c.Value = "EE" And Left((c.Offset(0, 14).Value), 3) = "ER0" Then c.Offset(0, 19).Value = 0: Exit For
            
        Next

    Thanks!


    • Moved by Carl Cai Wednesday, November 5, 2014 6:26 AM more related
    Tuesday, November 4, 2014 4:51 PM

Answers

  • Hi Beemer,

    Based on the code, the cell changed value is offset 19 to the active cell. And if you want to change the active cell in the looping, you can remove the offset. Here is the changed code for your reference:

      For Each c In isect ' check for Document# = ER0
            
            If c.Value = "EE" And Left((c.Offset(0, 14).Value), 3) = "ER0" Then c.Value = 0: Exit For
            
        Next
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 5, 2014 8:03 AM
    Moderator

All replies

  • Hello,

    Is this VB.NET or VBA (Office automation) ? If not VB.NET I can move your question to a better forum.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, November 4, 2014 6:57 PM
  • Hello,

    I have helped you move this issue to more related forum to get supports.

    Thanks for your understanding.

    Regards.

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 5, 2014 6:28 AM
  • Hi Beemer,

    Based on the code, the cell changed value is offset 19 to the active cell. And if you want to change the active cell in the looping, you can remove the offset. Here is the changed code for your reference:

      For Each c In isect ' check for Document# = ER0
            
            If c.Value = "EE" And Left((c.Offset(0, 14).Value), 3) = "ER0" Then c.Value = 0: Exit For
            
        Next
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 5, 2014 8:03 AM
    Moderator