VBA Selection

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
754
Hello Friends,

Is there a way to move the selection to the second column of that same row.

Like if the selection (target cell) is lets say column H32 then I would want the code to make the selection to B32 or if the selection (target cell) in on A15 then i would want the code to make the section to B15.

fName = "\\192.168.0.100\itex Share\ITEX\PO" & Range("T5").Value & ".pdf"


In above case the the file name is calling from Range T5. So what I want is that if my selection is anywhere in row # 5 then it should pick the second column i.e. B5.


Regards,

Humayun
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,788
Many ways, here's one:

Code:
    fName = "\\192.168.0.100\itex Share\ITEX\PO" & Cells(Range("T5").Row, "B").Value & ".pdf"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,803
Office Version
2007
Platform
Windows
Try this

Code:
fName = "\\192.168.0.100\itex Share\ITEX\PO" & Range("B" & activecell.row).Value & ".pdf"
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
754
Hi Eric & Dante

Thanks for the replies... I will surely try your solution

Meanwhile please have a look at this... I searched a bit on the net and found this solution


Code:
 fName = "\\192.168.0.100\itex Share\ITEX\DESIGNS\" & Cells(ActiveCell.Row, 2).Value & ".pdf"
I would like your thoughts on it.. Is there a better way ? working perfect though

Regards,

Humayun
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,788
That's essentially the same solution I presented. The only difference is that it uses the value 2 to indicate the column, while I used "B". They work the same, it's largely a matter of preference in this case. Some people might prefer the "B" because that's easier to recognize as a column identifier. But if you want to use a variable instead of a constant, or put it in a loop, using numbers instead of letters is far easier.

Anyway, glad we could help.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,803
Office Version
2007
Platform
Windows
They work the same:

Code:
Range([COLOR=#0000ff]"B"[/COLOR] & ActiveCell.row).Value

Cells(ActiveCell.Row, [COLOR=#0000ff]2[/COLOR]).Value

Cells(ActiveCell.Row, [COLOR=#0000ff]"B"[/COLOR]).Value
How to comment Eric is a matter of preference.
I prefer to use Range() because it allows me to use the Intellisense (is the drop down list that appears next to variables in the VBA).
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,803
Office Version
2007
Platform
Windows
Youre welcome & thanks for the feedback.
 

Forum statistics

Threads
1,077,955
Messages
5,337,391
Members
399,144
Latest member
Lauren Ward

Some videos you may like

This Week's Hot Topics

Top