Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 33

Thread: Use Offset to select offset cell value

  1. #1
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location

    Use Offset to select offset cell value

    Wish to use Offset VBA to select cell value in VBA macro below, if possible. Please.

    Sub Rep()
    Dim c As Range
    For Each c In Range("d12:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If c.Value = "1" Then c.Offset(, 1).Value = 1
    If c.Value = "2" Then c.Offset(, 1).Value = 2
    If c.Value = "4" Then c.Offset(, 1).Value = 4
    If c.Value = "5" Then c.Offset(c, 2).Select ‘Wrong
    If c.Value = "6" Then c.Offset(, 1).Value = 6
    Next c

    End Sub
    Column D Column E Column F
    ClockOut24 hr time
    Call Day
    1:BU 2:NE 4:WBU 5:WBU 6:KW
    Base Points
    1=1, 2=2, 4=4, 5= Weekend/Holiday Total Hrs @1.5, 6=6
    TotalPoints*
    Row 12
    4
    4
    Row 12
    5
    Valuecolumn F =57
    57

    If c.Value = "4" Then c.Offset(, 1).Value = 4 ‘working
    If c.Value = "5" Then c.Offset(, 2).Select ‘???? wish to select value of c.Offset(,2) which is 57

    Thanks.
    Last edited by Victor; 12-02-2021 at 12:00 PM.

  2. #2
    VBAX Contributor rollis13's Avatar
    Joined
    Jun 2013
    Location
    Cordenons
    Posts
    146
    Location
    Not sure since I have no idea of your sheet layout but in your macro shouldn't it be:
    If c.Value = "5" Then c.Offset(, 2).Select
    
    instead of:
    If c.Value = "5" Then c.Offset(c, 2).Select 'Wrong
    By the way, are you sure that the range in here is right:
    For Each c In Range("D12:A" & Range("A" & Rows.Count).End(xlUp).Row)
    
    Since I suppose you need only column D I would use:
    
    For Each c In Range("D12:D" & Range("A" & Rows.Count).End(xlUp).Row)

  3. #3
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location
    Thanks rollis13

    Works fine as suggested.

    Now I wish to display the value selected in cell F12 (57) in cell E12, when enter 5 in cell D12.

    Is this possible?

    Thanks for your interest in help me.

  4. #4
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Hi Victor,

    Maybe give the below a try, just a little note: it's never a great idea to use single letters for things like a range, maye consider using something like rCell as the reference cell within a range loop, keep the single letters to things like counters. You also should not need to use Select to achieve this and just make one cell equal another.

    Sub Rep()    
        Dim rCell As Range
        
        For Each rCell In Range("D12:D" & Range("A" & Rows.Count).End(xlUp).Row)
            Select Case rCell.Value
                Case 1
                rCell.Offset(, 1).Value = 1
                
                Case 2
                rCell.Offset(, 1).Value = 2
                
                Case 4
                rCell.Offset(, 1).Value = 4
                
                Case 5
                rCell.Offset(, 1).Value = rCell.Offset(, 2).Value
                
                Case 6
                rCell.Offset(, 1).Value = 6
            End Select
        Next rCell
    End Sub
    Hope this helps
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  5. #5
    VBAX Contributor rollis13's Avatar
    Joined
    Jun 2013
    Location
    Cordenons
    Posts
    146
    Location
    Then that code line would be:
    If c.Value = "5" Then c.Offset(, 1).Value = c.Offset(, 2).Value

  6. #6
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Yes but no need for the "" around the 5

    If c.Value = 5 Then c.Offset(, 1).Value = c.Offset(, 2).Value
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  7. #7
    VBAX Contributor rollis13's Avatar
    Joined
    Jun 2013
    Location
    Cordenons
    Posts
    146
    Location
    georgiboy, yes, but he put them and since I don't know his real project I leave them .

  8. #8
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Quote Originally Posted by rollis13 View Post
    georgiboy, yes, but he put them and since I don't know his real project I leave them .
    It's not an issue, I just like to point out what I see.

    It makes my OCD and Autism feel better...
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  9. #9
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Righto you two play nice..... Victor's presentation may have been a little complicated and you have both been very helpful in this issue. So, daggers back in there respective scabbards please.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Quote Originally Posted by Aussiebear View Post
    Righto you two play nice..... Victor's presentation may have been a little complicated and you have both been very helpful in this issue. So, daggers back in there respective scabbards please.
    My autism makes me write in a way that seems like I am always angry/ stressed, I am not (most of the time anyway), I am just very literal to the point it comes across in the wrong way
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    So @georgi

    Don't be cross with me; all you need is this:

    Sub M_snb()
      For Each it In Range("D12:D" & Range("A" & Rows.Count).End(xlUp).Row)
        it.offset(,1)= it.Value
        if it=5 then it.Offset(, 1) = it.Offset(, 2).Value
      Next
    End Sub
    @Aussie

    I don't see any daggers.

  12. #12
    VBAX Contributor rollis13's Avatar
    Joined
    Jun 2013
    Location
    Cordenons
    Posts
    146
    Location
    [OT]
    Quote Originally Posted by snb View Post
    @Aussie
    I don't see any daggers.
    I normally go around unarmed but since I'm bionic I can transform my forearm and hand into a scimitar .
    [/OT]

  13. #13
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I like them corroded.

  14. #14
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Quote Originally Posted by snb View Post
    I like them corroded.
    A ragged dagger... Anagram alert
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  15. #15
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location
    Thanks to

    1-rollis13
    for his promptly help and solution
    2-georgiboy
    for alternate solution and suggestions
    3-snb
    for the incredible short code alternate solution

    Finally to Aussiebear for his diplomatic intervention

    All your help me
    1-provided the solution to let me continue my macro project
    2-learned alternate ways to write macros

    Thanks to all for your help.

    Happy holidays to all!

    Victor

  16. #16
    VBAX Contributor rollis13's Avatar
    Joined
    Jun 2013
    Location
    Cordenons
    Posts
    146
    Location
    Glad we were able to help .

  17. #17
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Besides, I'd prefer:

    Sub M_snb()
      For Each it In usedrange.columns(4).offset(11).specialcells(2)
        it.offset(,1)= it.offset(,-2*(it = 5)).Value
      Next
    End Sub

  18. #18
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    To reduce worksheet interaction and speed up the macro:

    Sub M_snb()
      sn = usedrange.columns(4).offset(11).specialcells(2).resize(,3)
    
      For j=1 to ubound(sn)
         if sn(j,1)=5 then sn(j,1)=sn(j,3)
      Next
    
      cells(5,12).resize(ubound(sn))=sn
    End Sub

  19. #19
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location
    Thanks snb for the additional solutions recommendations. There are really interesting to analyze them and fully understand their format.

    As the saying said

    ’there is more than one way to skin a cat’

    Thanks again!, really appreciate your input.
    Last edited by Victor; 12-04-2021 at 12:35 PM.

  20. #20
    Re: ’there is more than one way to skin a cat’
    Due to political correctness required in todays sad world, it should be
    "All roads lead to Rome."
    After all, you don't want the world's cats upset for suggesting that they should be skinned.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •