Page 2 • Quick Excel Help • Non-Gaming • Software • Eurogamer.net

JetSetWilly 27 Jun 2007 17:13:14 5,720 posts
Seen 1 year ago
Registered 16 years ago

Is their a property or method on the Sheets object that returns how many sheets there are currently?

[code]Sheets("BLANK").Copy After:=Sheets.Count[/code]

More here Razz:

http://support.microsoft.com/kb/288402

Edited by JetSetWilly at 18:08:25 27-06-2007

MrWorf 27 Jun 2007 21:12:27 64,187 posts
Seen 12 hours ago
Registered 20 years ago

There isn't one. My code is very basic.

Aww cheers that works a treat.

)

Hit a slight snag though. I've coded it so the new copied worksheet is automatically named after the cell contents of Cell D2. But I'm worried if someone forgets to put that information in before submiting the form because if D2 is blank it breaks the code.

I've experimented with various ways of using IF/ELSE codes to fix this but to no avail.

Basically it's:
[code]Sub Macros ()

Sheets("BLANK").Select
IF D2 = "" THEN BREAK
ELSE NEXT
END IF
Sheets("BLANK").Copy After:=Sheets.Count
Sheets("BLANK (2)").Select
Sheets("BLANK (2)").Name = Worksheets("BLANK (2)").Range("D2").Value[/code]

But I'm struggling to put this in a way that VB will recognize. I've just sort guessed what it might be.

MrWorf 27 Jun 2007 21:35:55 64,187 posts
Seen 12 hours ago
Registered 20 years ago

Figured it out!

[code]
Sheets("BLANK").Select
If ActiveSheet.Range("D2").Value = "" Then
MsgBox ("Please enter Risk Assessment Number")
End
End If
Sheets("BLANK").Copy After:=Sheets(Sheets.Count)
Sheets("BLANK (2)").Select
Sheets("BLANK (2)").Name = Worksheets("BLANK (2)").Range("D2").Value
[/code]

MrWorf 21 Sep 2007 15:04:52 64,187 posts
Seen 12 hours ago
Registered 20 years ago

*BUMP*

MrWorf 21 Sep 2007 15:04:53 64,187 posts
Seen 12 hours ago
Registered 20 years ago

Argh! Anyone know if it's possible to paste the contents of a colum into a row? So instead of the data moving down the sheet I want to cut and paste it so it goes along the sheer.. possible wihtout cut and pasting every cell?

jellyhead 21 Sep 2007 15:06:00 24,355 posts
Seen 2 weeks ago
Registered 18 years ago

Razz wrote:
Argh! Anyone know if it's possible to paste the contents of a colum into a row? So instead of the data moving down the sheet I want to cut and paste it so it goes along the sheer.. possible wihtout cut and pasting every cell?

Does "Paste Special" allow that?

yay it does, Transpose is the kiddy apparently.

MrWorf 21 Sep 2007 15:11:38 64,187 posts
Seen 12 hours ago
Registered 20 years ago

\o/

Thanks mate!

MrWorf 21 Sep 2007 15:11:38 64,187 posts
Seen 12 hours ago
Registered 20 years ago

*bump*

MrWorf 26 Sep 2007 13:08:40 64,187 posts
Seen 12 hours ago
Registered 20 years ago

*bump*

MrWorf 26 Sep 2007 13:08:41 64,187 posts
Seen 12 hours ago
Registered 20 years ago

Please help me again!

I'm trying to write an IF statment for the following

=IF(B2=E2 or C2=E2 or D2=E2,TRUE,FALSE)

This doesn't work!

I just get NAME?

Basically if c1 matches either of the values in a1, b1 or c1 I want e1 to display true or false. I know this is possible but perhaps I'm going around it in the wrong way?

Please help! \o/

tannerd 26 Sep 2007 13:12:25 2,691 posts
Seen 1 month ago
Registered 18 years ago

Try:

=IF(OR(a1=d1, b1=d1, c1=d1),true,false)

MrWorf 26 Sep 2007 13:17:28 64,187 posts
Seen 12 hours ago
Registered 20 years ago

\o/

Cheers!

MrWorf 26 Sep 2007 13:17:29 64,187 posts
Seen 12 hours ago
Registered 20 years ago

Works a treat

DamoVotf 26 Sep 2007 14:18:16 1,465 posts
Seen 1 year ago
Registered 15 years ago

Micro_Explosion wrote:
/secretly has 80+meg excel files
/and files with calculations that takes 5 minutes each time

Noob, strokes 1gig excel file with 36 hr recalculation time.

Needs a super computer to open.

gang_of_bitches 19 Nov 2007 16:11:59 5,707 posts
Seen 2 years ago
Registered 16 years ago

OK, thought I was being terribly clever, that as it turned out was an error.

So I've got a very simple spreadsheet of sales by date running horizontally against the top against different lines runnuing vertically. Thing is I'm running out of room and now want to swap the two around. No problem I thought, I'll just do copy-paste special-transpose. But the problem is its not having it, says fields aren't the same size and shape. How do I get round this bollocks?

Lutz 19 Nov 2007 16:15:46 48,870 posts
Seen 4 years ago
Registered 18 years ago

IIRC you can't transpose rows with columns. I wanted to do the same years ago and couldn't.

gang_of_bitches 19 Nov 2007 16:17:28 5,707 posts
Seen 2 years ago
Registered 16 years ago

Lutz wrote:
IIRC you can't transpose rows with columns. I wanted to do the same years ago and couldn't.

Arse, I was under the impression that was exactly what it was supposed to do. What the hell does it do then?

gang_of_bitches 19 Nov 2007 16:17:45 5,707 posts
Seen 2 years ago
Registered 16 years ago

Lutz wrote:
IIRC you can't transpose rows with columns. I wanted to do the same years ago and couldn't.

Arse, I was under the impression that was exactly what it was supposed to do. What the hell does it do then?

Oh and thanks.

ming 19 Nov 2007 16:19:13 275 posts
Seen 7 years ago
Registered 15 years ago

If you have more then 256 rows then it wont work as Lutz said. However this error is normally because you have one or more merged cells and the paste area is not the same. Just unmerge the cells and fix the formatting afterwards.

Lutz 19 Nov 2007 16:19:41 48,870 posts
Seen 4 years ago
Registered 18 years ago

I dunno, it's not even in my Paste Special options.
Which excel you using?

Lutz 19 Nov 2007 16:21:15 48,870 posts
Seen 4 years ago
Registered 18 years ago

Oh shit! YEah you can.

But you can't do it with a whle row or a column in one go.
hang on.

Edit: Right. Data in A1:A4. If you select copy on A1:A4 you can't paste into A1, B1, C1, D1, because A1 is in both copy and paste. You could paste into B1, C1, D1, E1 though.

And due to that by default you can't do a whole row or column in one go.

You may be able to do a row into a column actually (256 lines into 65536 should go)

gang_of_bitches 19 Nov 2007 16:21:31 5,707 posts
Seen 2 years ago
Registered 16 years ago

Some old shit one, possibly 98, certainly pre-xp prettiness revamp.

For me it's the bottom right tick-box in paste special.

gang_of_bitches 19 Nov 2007 16:27:36 5,707 posts
Seen 2 years ago
Registered 16 years ago

Lutz wrote:
Oh shit! YEah you can.

But you can't do it with a whle row or a column in one go.
hang on.

Edit: Right. Data in A1:A4. If you select copy on A1:A4 you can't paste into A1, B1, C1, D1, because A1 is in both copy and paste. You could paste into B1, C1, D1, E1 though.

And due to that by default you can't do a whole row or column in one go.

You may be able to do a row into a column actually (256 lines into 65536 should go)

Ah that kinda makes sense, its only pretty small at the moment and I was only trying to switch cells rather than full rows and columns. Oh well ho hum, I'll just hide cells as info gets old.

MrWorf 22 Nov 2007 15:26:01 64,187 posts
Seen 12 hours ago
Registered 20 years ago

Aw crap! My calculations aren' automatically updating! ;_;

/double clicks cells for if statements to update

I know there must be a setting somewhere that forces Excel to update all calculations automatically... is there?

Please halp! (._.)

Edit: Auto not manual!

JetSetWilly 22 Nov 2007 15:27:59 5,720 posts
Seen 1 year ago
Registered 16 years ago

Tools->Options->Calculation->Select Automatic

Lutz 22 Nov 2007 15:28:39 48,870 posts
Seen 4 years ago
Registered 18 years ago

F9

MrWorf 22 Nov 2007 15:28:40 64,187 posts
Seen 12 hours ago
Registered 20 years ago

\o/

/kisses JSW in the face!

Lutz 22 Nov 2007 15:29:07 48,870 posts
Seen 4 years ago
Registered 18 years ago

/wants a sloppy kiss too

NO TONGUES.

MrWorf 22 Nov 2007 15:30:13 64,187 posts
Seen 12 hours ago
Registered 20 years ago

/puts Lutz on shoulders

Oy, spread your arms and close you eyes

/runs around making plane noises

JetSetWilly 22 Nov 2007 15:31:19 5,720 posts
Seen 1 year ago
Registered 16 years ago

Razz wrote:
\o/

/kisses JSW in the face!

Aaww shucks

/blushes

Quick Excel Help • Page 2