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