Use Offset to select offset cell value

[Victor]

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.

[rollis13]

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)

[Victor]

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.

[georgiboy]

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

[rollis13]

Then that code line would be:
If c.Value = "5" Then c.Offset(, 1).Value = c.Offset(, 2).Value

[georgiboy]

Yes but no need for the "" around the 5


If c.Value = 5 Then c.Offset(, 1).Value = c.Offset(, 2).Value

[rollis13]

georgiboy (http://www.vbaexpress.com/forum/member.php?15252-georgiboy), yes, but he put them and since I don't know his real project I leave them :dunno.

[georgiboy]

georgiboy (http://www.vbaexpress.com/forum/member.php?15252-georgiboy), yes, but he put them and since I don't know his real project I leave them :dunno.

It's not an issue, I just like to point out what I see.

It makes my OCD and Autism feel better... :rofl:

[Aussiebear]

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.

[georgiboy]

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.

:rofl: 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 :bug:

[snb]

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.

[rollis13]

@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 :rofl::p.

[snb]

I like them corroded.

[georgiboy]

I like them corroded.

A ragged dagger... Anagram alert

[Victor]

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

[rollis13]

Glad we were able to help :hi:.

[snb]

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

[snb]

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

[Victor]

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.

[jolivanes]

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.

[Victor]

Thanks jolivanes, definitely ,”all roads lead to Rome”.

[Victor]

Jolivanes, Definitely, “all roads lead to Rome”.

[snb]

A compromise ?

'All cats walk to Rome' ?

[Victor]

Snb, you should work for the United Nations solving countries issues. Your are a diplomatic by nature!
But as all in life some will not agree and say that they have been taken out of that statement. You can not compromised all.
Happy holidays to all.!

[jolivanes]

Re: 'All cats walk to Rome' ?
How about "zwarte pieten"?

[Aussiebear]

Millennial cats wont walk these days.... and Rome is so "yesterday". So in an effort to finalise this conversation, can we all agree;
that feline variety animals commonly referred to as "Cats, be they he/she it or sovereign citizen , may for what ever reason (Physically, mentally or pixellated) find, recognise, themselves geographically relocated. The method of relocation may be or may not be preferred, authorised, endorsed, stylish, emotionally or financially sufficient, may or may not be to a geo-specific location of their choosing, social, economic, cultural, religious, or climatic preference for a period of yet to be determined time.

For those of you who have nothing better to do, try putting that into VBA in another thread.

[Victor]

Not acceptable because of the implications, since

this notorious Christmas character is dividing the country, specially Netherlands.

[Victor]

Aussiebbear

touché

[snb]

this notorious Christmas character is dividing the country, specially Netherlands.

That's not correct. It is not a Christmas character.

[jolivanes]

Victor.
FYI, it is the same as in the rest of the world. A small minority makes the most noise because the majority, that is working, does not have time to protest.
Delve deeper in to it, including the background of the person that started this and if you might think different if you are a person that lives in the real world rather then a dream world.
This is nothing personal, just my view of the current situation in the western part of this world.

[Victor]

I may agree or not agree with people in general but respect what they say and I will defend the right to say it, as someone said.

Sorry if I dot not express myself clearly in my comments. Just try to be friendly with the people who help me.

[jolivanes]

No problem Victor.
We love all people equally. (Maybe some a little more than others but that depends on what they're wearing, skirts or trousers!!!)

[Victor]

And everybody lived happily ever after.

The end!