I've been trying to make a minor edit to a simple XLM macro which I
wrote many years ago. On running the macro from any cell it should
select the cell in col B of the same row.

FWIW, this shows the worksheet MACROGEN-3.XLM after unhiding it.

https://dl.dropboxusercontent.com/u/...lSameRow-1.jpg

I thought I could now edit it from

GotoColA
=COLUMN(ACTIVE.CELL())
=SELECT("rc["&-(ColA-1)&"]")
=RETURN()

to this

GotoColB
=COLUMN(ACTIVE.CELL())
=SELECT("rc["&-(ColB-1)&"]")
=RETURN()

But, without going into detail, I failed on several counts. So I'll
abandon that; even though many of those old macros appear to work, it
seems pointless to attempt to maintain or edit them.

Could I therefore get some help on a modern VBA macro to do it please?
Which presumably I should insert in PERSONAL.XLS (or PERSONAL.XLSM if
I rename it as I intend.)

--------------------

I did look for a KB shortcut and also tried using the GoTo dialog, but
failed there too.

--
Terry, East Grinstead, UK

hoahồng mạ vÃ*ng dùng lÃ*m quÃ* tặng cho các gia đình, hoa hồng mạ vÃ*ng 24k có đế chữ love thân gá»*i vợ cÅ©, 0966.85.0966

I use local scope (sheet level) col-absolute/row-relative defined names
for this. For example...

A1: Qty; B1: Item ID; C1: Description; D1: Unit Price; E1: Amount

...and I select A1 and define names as follows...

Name: sheet1!Qty RefersTo: =$A1
Name: sheet1!ItemID RefersTo: =$B1
Name: sheet1!Descr RefersTo: =$C1
Name: sheet1!UnitPrice RefersTo: =$D1
Name: sheet1!Amount RefersTo: =$E1

...where cols A:D are not locked, the sheet is protected.

The 'Amount' col gets the following formula:
=IF(UnitPrice<"",ROUND(UnitPrice*Qty,2),"")
Used in row 2: =IF($D2<"",ROUND($D2*$A2),2),"")
Used in row 9: =IF($D9<"",ROUND($D9*$A9),2),"")

The 'Description' col gets the folowing formula:
=IF(ItemID="","",VLOOKUP(ItemID,PriceList,Descript ion,FALSE))

The 'Unit Price' col gets the following formula:
=IF(OR(ItemID="",Qty=""),"",VLOOKUP(ItemID,PriceLi st,Price,FALSE))

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

GS wrote:

I use local scope (sheet level) col-absolute/row-relative defined names
for this. For example...

A1: Qty; B1: Item ID; C1: Description; D1: Unit Price; E1: Amount

..and I select A1 and define names as follows...

Name: sheet1!Qty RefersTo: =$A1
Name: sheet1!ItemID RefersTo: =$B1
Name: sheet1!Descr RefersTo: =$C1
Name: sheet1!UnitPrice RefersTo: =$D1
Name: sheet1!Amount RefersTo: =$E1

..where cols A:D are not locked, the sheet is protected.

The 'Amount' col gets the following formula:
=IF(UnitPrice<"",ROUND(UnitPrice*Qty,2),"")
Used in row 2: =IF($D2<"",ROUND($D2*$A2),2),"")
Used in row 9: =IF($D9<"",ROUND($D9*$A9),2),"")

The 'Description' col gets the folowing formula:
=IF(ItemID="","",VLOOKUP(ItemID,PriceList,Descript ion,FALSE))

The 'Unit Price' col gets the following formula:
=IF(OR(ItemID="",Qty=""),"",VLOOKUP(ItemID,PriceLi st,Price,FALSE))

HTH

I've just realised that the intuitive keystrokes Home ArrowRight
ArrowRight also do it!

--
Terry, East Grinstead, UK

GS wrote:

I use local scope (sheet level) col-absolute/row-relative defined
names for this. For example...

A1: Qty; B1: Item ID; C1: Description; D1: Unit Price; E1:
Amount

..and I select A1 and define names as follows...

Name: sheet1!Qty RefersTo: =$A1
Name: sheet1!ItemID RefersTo: =$B1
Name: sheet1!Descr RefersTo: =$C1
Name: sheet1!UnitPrice RefersTo: =$D1
Name: sheet1!Amount RefersTo: =$E1

..where cols A:D are not locked, the sheet is protected.

The 'Amount' col gets the following formula:
=IF(UnitPrice<"",ROUND(UnitPrice*Qty,2),"")
Used in row 2: =IF($D2<"",ROUND($D2*$A2),2),"")
Used in row 9: =IF($D9<"",ROUND($D9*$A9),2),"")

The 'Description' col gets the folowing formula:
=IF(ItemID="","",VLOOKUP(ItemID,PriceList,Descript ion,FALSE))

The 'Unit Price' col gets the following formula:
=IF(OR(ItemID="",Qty=""),"",VLOOKUP(ItemID,PriceLi st,Price,FALSE))

HTH

I've just realised that the intuitive keystrokes Home ArrowRight
ArrowRight also do it!

How does that automate your macro?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

GS wrote:

GS wrote:

I use local scope (sheet level) col-absolute/row-relative defined
names for this. For example...

A1: Qty; B1: Item ID; C1: Description; D1: Unit Price; E1:
Amount

..and I select A1 and define names as follows...

Name: sheet1!Qty RefersTo: =$A1
Name: sheet1!ItemID RefersTo: =$B1
Name: sheet1!Descr RefersTo: =$C1
Name: sheet1!UnitPrice RefersTo: =$D1
Name: sheet1!Amount RefersTo: =$E1

..where cols A:D are not locked, the sheet is protected.

The 'Amount' col gets the following formula:
=IF(UnitPrice<"",ROUND(UnitPrice*Qty,2),"")
Used in row 2: =IF($D2<"",ROUND($D2*$A2),2),"")
Used in row 9: =IF($D9<"",ROUND($D9*$A9),2),"")

The 'Description' col gets the folowing formula:
=IF(ItemID="","",VLOOKUP(ItemID,PriceList,Descript ion,FALSE))

The 'Unit Price' col gets the following formula:
=IF(OR(ItemID="",Qty=""),"",VLOOKUP(ItemID,PriceLi st,Price,FALSE))

HTH

I've just realised that the intuitive keystrokes Home ArrowRight
ArrowRight also do it!

How does that automate your macro?

The simple answer is: it doesn't. But a more helpful explanation if
you're curious is a lot longer!

You may recall that I use a macro program called Macro Express Pro.
That not only avoids programming in the strict sense of the term
(including VBA coding) but allows me to perform more complex tasks
than VBA allows, working across applications, not just in Excel.

I wrote one a couple of years ago for Excel 2000 and the present
discussion is about editing it for Excel 365. In summary it works as
illustrated here and explained below:

https://dl.dropboxusercontent.com/u/...erything-1.jpg

1. With the current Excel selection anywhere in the row containing
details of a walk (hike) I use an assigned hotkey to run it. (Or a
pop-up menu containing tasks related exclusively to Excel.)

2. That opens another program, Everything, which searches for
'Finished Walk' files (in a certain folder, on either of two HDs)
starting with that date. (All my files relating to walks are named
with the prefix YYYYMMDD.)

3. It then allows me to choose the required file if there are more
than one.

4. I d-click the choice and the correct image opens.

Coming back to the Excel macro...

For step #1 above, I had the selection of col B working by simulating
keystrokes with the MX macro; no Excel macro involved. But it appeared
that this didn't work in Excel 365. Hence my request for help with an
Excel macro. However, when I tested it later I found to my surprise
that Home -- -- worked.

--------------------

Bringing this right up to date, I've now realised that the
inconsistency arises because I've changed the sheet I'm working with.
As a precaution at some point yesterday I copied a section of the
original to a new sheet (new tab) called 'Testing'. After much head
scratching I now see that the original had a pane frozen, while
Testing does not. Those keystrokes fail with a frozen pane because
Home selects the first column outside the frozen ones.

So I do after all need an Excel macro to include in step 1 of my MX
macro.

The simplest I've come up with so far is:

Sub GoToB2()
ActiveCell.EntireRow.Range("b1").Select
End Sub


--
Terry, East Grinstead, UK

GS wrote:

GS wrote:

I use local scope (sheet level) col-absolute/row-relative defined
names for this. For example...

A1: Qty; B1: Item ID; C1: Description; D1: Unit Price; E1:
Amount

..and I select A1 and define names as follows...

Name: sheet1!Qty RefersTo: =$A1
Name: sheet1!ItemID RefersTo: =$B1
Name: sheet1!Descr RefersTo: =$C1
Name: sheet1!UnitPrice RefersTo: =$D1
Name: sheet1!Amount RefersTo: =$E1

..where cols A:D are not locked, the sheet is protected.

The 'Amount' col gets the following formula:
=IF(UnitPrice<"",ROUND(UnitPrice*Qty,2),"")
Used in row 2: =IF($D2<"",ROUND($D2*$A2),2),"")
Used in row 9: =IF($D9<"",ROUND($D9*$A9),2),"")

The 'Description' col gets the folowing formula:
=IF(ItemID="","",VLOOKUP(ItemID,PriceList,Descript ion,FALSE))

The 'Unit Price' col gets the following formula:

=IF(OR(ItemID="",Qty=""),"",VLOOKUP(ItemID,PriceLi st,Price,FALSE))

HTH

I've just realised that the intuitive keystrokes Home ArrowRight
ArrowRight also do it!

How does that automate your macro?

The simple answer is: it doesn't. But a more helpful explanation if
you're curious is a lot longer!

You may recall that I use a macro program called Macro Express Pro.
That not only avoids programming in the strict sense of the term
(including VBA coding) but allows me to perform more complex tasks
than VBA allows, working across applications, not just in Excel.

I wrote one a couple of years ago for Excel 2000 and the present
discussion is about editing it for Excel 365. In summary it works as
illustrated here and explained below:

https://dl.dropboxusercontent.com/u/...erything-1.jpg

1. With the current Excel selection anywhere in the row containing
details of a walk (hike) I use an assigned hotkey to run it. (Or a
pop-up menu containing tasks related exclusively to Excel.)

2. That opens another program, Everything, which searches for
'Finished Walk' files (in a certain folder, on either of two HDs)
starting with that date. (All my files relating to walks are named
with the prefix YYYYMMDD.)

3. It then allows me to choose the required file if there are more
than one.

4. I d-click the choice and the correct image opens.

Coming back to the Excel macro...

For step #1 above, I had the selection of col B working by simulating
keystrokes with the MX macro; no Excel macro involved. But it
appeared that this didn't work in Excel 365. Hence my request for
help with an Excel macro. However, when I tested it later I found to
my surprise that Home -- -- worked.

--------------------

Bringing this right up to date, I've now realised that the
inconsistency arises because I've changed the sheet I'm working with.
As a precaution at some point yesterday I copied a section of the
original to a new sheet (new tab) called 'Testing'. After much head
scratching I now see that the original had a pane frozen, while
Testing does not. Those keystrokes fail with a frozen pane because
Home selects the first column outside the frozen ones.

So I do after all need an Excel macro to include in step 1 of my MX
macro.

The simplest I've come up with so far is:

Sub GoToB2()
ActiveCell.EntireRow.Range("b1").Select
End Sub

Terry, absolutely everything you state here can be done in Excel with
VBA! Not sure why you say it can't...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

You may recall that I use a macro program called Macro Express Pro.

Sorry, but I did not know this. I did browse the website, though, and
have downloaded Pro for a test drive. This looks like an excellent
utility for personal use by power users whom are non-programmers!

However, I do application development in VB6 or Excel VBA, but I often
use vbScript for lightweight personal utilities. All of my Excel apps
have been duped as VB6 stand-alone EXEs via using the Farpoint
Spread.ocx ActiveX Spreadsheet control. This was done to facilitate non
MS Office users having my apps. (Many of my users switched away from
MSO when the Ribbon interface was introduced!)

While there's nothing yet that I haven't been able to make Excel/VB6
do, Macro Express Pro looks like a formidable tool worth adding to
anyone's arsenal!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

GS wrote:

You may recall that I use a macro program called Macro Express Pro.

Sorry, but I did not know this. I did browse the website, though, and
have downloaded Pro for a test drive. This looks like an excellent
utility for personal use by power users whom are non-programmers!

However, I do application development in VB6 or Excel VBA, but I often
use vbScript for lightweight personal utilities. All of my Excel apps
have been duped as VB6 stand-alone EXEs via using the Farpoint
Spread.ocx ActiveX Spreadsheet control. This was done to facilitate non
MS Office users having my apps. (Many of my users switched away from
MSO when the Ribbon interface was introduced!)

While there's nothing yet that I haven't been able to make Excel/VB6
do, Macro Express Pro looks like a formidable tool worth adding to
anyone's arsenal!

Yep, given my lack of (modern) programming skills, MX Pro fills the
gap ;-)

--
Terry, East Grinstead, UK

GS wrote:

You may recall that I use a macro program called Macro Express Pro.

Sorry, but I did not know this. I did browse the website, though,
and have downloaded Pro for a test drive. This looks like an
excellent utility for personal use by power users whom are
non-programmers!

However, I do application development in VB6 or Excel VBA, but I
often use vbScript for lightweight personal utilities. All of my
Excel apps have been duped as VB6 stand-alone EXEs via using the
Farpoint Spread.ocx ActiveX Spreadsheet control. This was done to
facilitate non MS Office users having my apps. (Many of my users
switched away from MSO when the Ribbon interface was introduced!)

While there's nothing yet that I haven't been able to make Excel/VB6
do, Macro Express Pro looks like a formidable tool worth adding to
anyone's arsenal!

Yep, given my lack of (modern) programming skills, MX Pro fills the
gap ;-)

The more time I spend reading its CHM and test driving the
SampleMacros, the more I'm liking it!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus