Replies

Dang … that was easy … yep – it worked

I’d spent about an hour looking for this and for answers on the web – well done WOW

Thanks Hans

Such a neat idea.

It works really well. Thank you – I think that this is THE answer.

I guess to be neat a bit ov VBA to do the sort but it could be a ‘recorded’ event.

Thnaks

You’re right (of course) !

Much neater. I’ve not come across Offset before – handy.

Thanks for the tip

Bob

The students I’m teaching are New to Excel and have never seen VBA before and I don’t want to do any “Just type this in” exercises. I like your code and may well use it myself however.
What I’ve done is taken your basic idea of the array though.
Ive written out every variation of 1234 and indexed it eg:
1-1-2-3-4
2-1-2-4-3
3-1-3-2-4
4-1-3-4-2-
etc (it gives 24 variations)
I’ve then used Roundup(Rand()*24 ,0) to get a number between 1 and 24 and used 4 lookups to read in the appriopriate numbers – seems to work

Bob

WOW!

Looks brilliant but I’m looking for something to do with relative novices. Either all within functions (as per my ugly method) or in very simple VBA if possible.

No

Must always be 1234 but in different orders each time

I’ve been teaching Excel recently and have quite a ‘fun’ idea. What about recording spot heights (contour heights) over and area then using the surface map to create a 3D relief map of your local area?

A bit different to the norm I think?

Bob

But … how do you get alternating shaded cells in the first place? I’ve tried to do this dozens of times with conditional formatting and have been successful but it’s been very tortuious.

Thanks for that, it seems to work but it still leaves the other pictures on the worksheet. Isn’t it possible to delete a picture – I mean I loaded it in so I must be able to delete it?

I just can’t get it to work for me and I’ve tried and tried. I am a beginner so I’ve never actually done it at all. Someone wouldn’t like (mind) puttin together an example for me would they?
eg
Sub message(text)
msgbox(text)
end sub

then place a command button on a page assigned to macro:
Message (“hello world”).

Bob

I’ve a similar problem. I’d like to do something like:

Sub PutNumber(Number)
Range(“B3”).Select
ActiveCell.Value = Number
End Sub

Then have a button on my workbook that would be attached to a macro called PutNumber 5 for example. But this doesn’t work. Can you actually define a variable in this way? if not then what are the ()s for?

Bob

Couldn’t you use Match(Max(range) to find it’s location then index it? Something like
Index(Match(Max(range))) to get what is there – not a good example but an idea?

Bob

I don’t really want to get into the complexity of your particular example but I’ve done something similar using AND and nested if’s
eg
IF(AND(A1>5,B2>5),good,If(AND(A1<5,B2<5),Bad,"You Got 5"))
or something like that. Check out AND in help

Bob

The ways round a problem can be very helpful – this certainaly looks as if it will work but …
I am a teacher and am looking for a solution that I can roll out over an intranet for children to use. I guess it’ll have to be a macro. I wondered if anyone had done it before – a ‘professional’ looking one with confirmation boxes etc.

Bob
Bob

Thanks Charles. There’s loads of info here. I’m sure I’ll be able to sort it now.