VBA fault finding
 

Hi, as you can probably see this is my first posting here.
I've only just been tasked to get to grips with VBA and things ar
progressing slowly.

My main problem at this moment is that I am trying to decipher
problem in this Excel 2000 tool that a co-worker built. Unfortunatel
the guy was extremely talented and is no longer with us (passed away).

I think I may have identified an area that might be wrong but I simpl
to not have the experience to know for certain (and find a solution)

Here's the code in question:


Sheets("Client Matrix").Select
Application.Goto Reference:="EnChData"
Selection.Copy
Sheets("Engine List").Select
*Range("B5").Select
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False*
Range("A5").Select
Sheets("Client Matrix").Select
Application.CutCopyMode = False
Range("AQ1").Select

The data at 'EnChData' is there where it should be and I believe th
enboldened bit goes to the 'Engine List' sheet, selects B5, moves dow
to the next blank cell and then pastes the information fro
'EnChData'.

It doesn't work. Nothing gets pasted. I don't know if it's this
'ActiveCell.Offset(1, 0).Range("A1").Select' that is the proble
because it doesn't make sense to me.

Any help would be appreciated.

Cheers

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=46594

VBA fault finding
 

Dim rng as Range
set rng = Sheets("Engine List").Range("B5")
do until rng = ""
set rng = rng.offset(1,0)
Loop
Sheets("Client Matrix").Range("EnChData").Copy
rng.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Sheets("Client Matrix").Select
Application.CutCopyMode = False
Range("AQ1").Select

--
Regards,
Tom Ogilvy




"Daminc" wrote in
message ...

Hi, as you can probably see this is my first posting here.
I've only just been tasked to get to grips with VBA and things are
progressing slowly.

My main problem at this moment is that I am trying to decipher a
problem in this Excel 2000 tool that a co-worker built. Unfortunately
the guy was extremely talented and is no longer with us (passed away).

I think I may have identified an area that might be wrong but I simply
to not have the experience to know for certain (and find a solution)

Here's the code in question:


Sheets("Client Matrix").Select
Application.Goto Reference:="EnChData"
Selection.Copy
Sheets("Engine List").Select
*Range("B5").Select
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False*
Range("A5").Select
Sheets("Client Matrix").Select
Application.CutCopyMode = False
Range("AQ1").Select

The data at 'EnChData' is there where it should be and I believe the
enboldened bit goes to the 'Engine List' sheet, selects B5, moves down
to the next blank cell and then pastes the information from
'EnChData'.

It doesn't work. Nothing gets pasted. I don't know if it's this:
'ActiveCell.Offset(1, 0).Range("A1").Select' that is the problem
because it doesn't make sense to me.

Any help would be appreciated.

Cheers.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=465942

VBA fault finding
 

Cheers, Tom.

Could you please tell what was actually wrong with the orginal coding
and what your code did to rectify it please.

It'll will help me learn to read it and hopefully allow me to spot any
similar mistakes.

Thanks for taking the time out :)


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=465942

VBA fault finding
 

You said the code works, but nothing is pasted. I suspect you are getting a
secondary effect which is clearing the clipboard before you attempt to
paste. That is just a guess. I gave you code which should minimize the
chance of such a secondary effect.

You can get code like you have by turning on the macro recorder and
recording your actions.

--
Regards,
Tom Ogilvy

"Daminc" wrote in
message ...

Cheers, Tom.

Could you please tell what was actually wrong with the orginal coding
and what your code did to rectify it please.

It'll will help me learn to read it and hopefully allow me to spot any
similar mistakes.

Thanks for taking the time out :)


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=465942

VBA fault finding
 

It doesn't work. Nothing gets pasted. I don't know if it's this
'ActiveCell.Offset(1, 0).Range("A1").Select' that is the proble
because it doesn't make sense to me.

I mentioned that the data at 'EnChData' was there. There are a lot o
macros before and after this one. I thought the error was probably i
this area because the data in 'EnChData' wasn't being pasted.

The clearing of the clipboard is something I hadn't considered whic
I'll look into, thanks.

I've been looking through a number of other threads and I've notice
the habit of many to post solutions but rarely any explainations. I
would be nice to have a tutor but at the moment I'm trying to learn VB
from practical exploration and PDF's. I've ordered a book to help bu
it's slow going

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=46594

VBA fault finding
 

Most people asking questions have at least a working knowledge of VBA or are
willing to sit down and figure out things they are not familiar with. If
they can't figure it out, they may post back with specific questions.

In your case, you might say in your original question, you would appreciate
some comments in any code provided as you are trying to learn.

Here are some links:



http://www.mvps.org/dmcritchie/excel/getstarted.htm


http://www.mvps.org/dmcritchie/excel....htm#tutorials
The VBA tutorials are listed after the general excel tutorials

http://msdn.microsoft.com/office/und...d/default.aspx


Try the microsoft Knowledge Base

http://support.microsoft.com/?id=159619

replace the 159619 with one of these numbers:

159619 XL97: Sample Macros for Customizing Menus and Submenus

161215 XL97: How to Programmatically Create a Collection

120198 XL: How to Select Cells/Ranges Using Visual Basic Procedures

141691 XL: Visual Basic Example Using If and Case Statements

146055 XL: Using Visual Basic to Create a Chart Using a Dynamic Range

141762 XL: How to Use Looping Structures in Visual Basic for Applications

143345 XL: Visual Basic Macro to Determine Which Button was Selected

--
Regards,
Tom Ogilvy


"Daminc" wrote in
message ...

It doesn't work. Nothing gets pasted. I don't know if it's this:
'ActiveCell.Offset(1, 0).Range("A1").Select' that is the problem
because it doesn't make sense to me.

I mentioned that the data at 'EnChData' was there. There are a lot of
macros before and after this one. I thought the error was probably in
this area because the data in 'EnChData' wasn't being pasted.

The clearing of the clipboard is something I hadn't considered which
I'll look into, thanks.

I've been looking through a number of other threads and I've noticed
the habit of many to post solutions but rarely any explainations. It
would be nice to have a tutor but at the moment I'm trying to learn VBA
from practical exploration and PDF's. I've ordered a book to help but
it's slow going.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=465942

VBA fault finding
 

Thanks Tom. I shall certainly work my way through those links.

I'm definitely going to learn this language one way or another :)

The book I've ordered is 'Excel VBA Macro Programming' by Richar
Shepherd.

Have you come across this book

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=46594