Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 259
Default In need of more guidance please....

Hi All

This line of code has served me well to date in other workbooks I have
created, that said.! not so in this particular book that is causing me to
spend alot of time here asking for help....

Columns("Y").Find("", Cells(Rows.Count, "Y"), xlValues, _
xlWhole, , xlNext).Select

In the following code it works well on the first pass placing everything
where it should be, but then when I hit it again, it overwrites the first,
when it is supposed to go to the next available blank cell below.

This particular piece of code only needs to copy into a range of Rows 10 to
19.

So, first entry goes into "Y10" and then copies the offset values.
Then if the user has another entry to add to it, it should drop down to the
next available blank cell which would be "Y11", and do it's thing there and
so on....

Sub Go_Runsheet()

With Application
.ScreenUpdating = False
End With

Sheets("RunSheet P1").Select

Columns("Y").Find("", Cells(Rows.Count, "Y"), xlValues, _
xlWhole, , xlNext).Select

ActiveCell.Select
With Selection
.Value = Sheets("Run Setup").Range("D2").Value
End With

ActiveCell.Offset(0, 55).Select
With Selection
.Value = Sheets("Run Setup").Range("A2").Value
End With

ActiveCell.Offset(0, 1).Select
With Selection
.Value = Sheets("Run Setup").Range("B2").Value
End With

ActiveCell.Offset(0, 1).Select
With Selection
.Value = Sheets("Run Setup").Range("F2").Value
End With

Range("AU30").Select

With Selection
.Value = Sheets("Run Setup").Range("E2").Value
End With

With Application
.ScreenUpdating = True
End With


End Sub

As always

Many thanks in advance for any asistance you can afford me..

Cheers
Mick.


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 259
Default In need of more guidance please....

Just to add and expand on the example.

The values being copied across all come from the same range on Sheets("Run
Setup").

When the user inputs her/his 1st entry, they clear the fields and enter
their 2nd entry etc....

Conversely, these entries also get copied across to the newly changed
Numeric Value Sheets that Garry & Gord helped me with...

Cheers


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default In need of more guidance please....

Vacuum Sealed pretended :
Just to add and expand on the example.

The values being copied across all come from the same range on Sheets("Run
Setup").

When the user inputs her/his 1st entry, they clear the fields and enter their
2nd entry etc....

Conversely, these entries also get copied across to the newly changed Numeric
Value Sheets that Garry & Gord helped me with...


FYI: About the renaming to numeric text...
This worked for me using your sheetnames and so I don't understand why
you had to change their names to numeric values. For example, your
source sheet named "Run Setup" worked fine for getting the sheetname
stored in B2.

Cheers


Try...

Sub Go_Runsheet2()
Dim rngFoundCell As Range
Application.ScreenUpdating = False
Set rngFoundCell = _
Sheets("RunSheet P1").Columns("Y").Find("", Cells(Rows.Count, "Y"),
_
xlValues, xlWhole, , xlNext).Select
If Not rngFoundCell Is Nothing Then
With rngFoundCell
.Value = Sheets("Run Setup").Range("D2").Value
.Offset(0, 55).Value = Sheets("Run Setup").Range("A2").Value
.Offset(0, 1).Value = Sheets("Run Setup").Range("B2").Value
.Offset(0, 1).Value = Sheets("Run Setup").Range("F2").Value
.Range("AU30").Value = Sheets("Run Setup").Range("E2").Value
End With
End If
Application.ScreenUpdating = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 259
Default In need of more guidance please....

Hi Garry

The code hangs on:

Set rngFoundCell = _
Sheets("RunSheet P1").Columns("Y").Find("", Cells(Rows.Count, "Y"),
xlValues, xlWhole, , xlNext).Select

Mouse over shows "rngFoundCell=Nothing"

Thx again.
Mick.


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 259
Default In need of more guidance please....

How odd.

It seems I no longer need help with this section of the code as it's working
as it should....

Sorry for the tme waste on this one....

Gremlins in my system I think.... :-/

Cheers
Mick.




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default In need of more guidance please....

Vacuum Sealed formulated on Sunday :
Hi Garry

The code hangs on:

Set rngFoundCell = _
Sheets("RunSheet P1").Columns("Y").Find("", Cells(Rows.Count, "Y"),
xlValues, xlWhole, , xlNext).Select

Mouse over shows "rngFoundCell=Nothing"

Thx again.
Mick.


Yeah, not surprised! I forgot to remove '.Select' from the end of that
line. Sorry about that!

Here's the revision that should work without issue...

Sub Go_Runsheet2()
Dim rngFoundCell As Range
Application.ScreenUpdating = False
Set rngFoundCell = _
Sheets("RunSheet P1").Columns("Y").Find("", _
Cells(Rows.Count, "Y"), xlValues, xlWhole, , xlNext)
If Not rngFoundCell Is Nothing Then
With rngFoundCell
.Value = Sheets("Run Setup").Range("D2").Value
.Offset(0, 55).Value = Sheets("Run Setup").Range("A2").Value
.Offset(0, 1).Value = Sheets("Run Setup").Range("B2").Value
.Offset(0, 1).Value = Sheets("Run Setup").Range("F2").Value
.Range("AU30").Value = Sheets("Run Setup").Range("E2").Value
End With
End If
Application.ScreenUpdating = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 259
Default In need of more guidance please....

Thx Garry

I actually didn't pick up on it either...

Don't mean to drag this out, but there is still a problem with it placing
the values in the wrong cells.

The first & second Offset Values are not showing at all and the third Offset
is displaying in a cell that is not part of the code.....very odd.....

On a brighter note and from a practical point, I have my origianl code
working, that said, it's not as short or savvy as yours.

Thx again for your efforts.

Cheers
Mick.



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default In need of more guidance please....

Vacuum Sealed explained on 6/20/2011 :
Thx Garry

I actually didn't pick up on it either...

Don't mean to drag this out, but there is still a problem with it placing the
values in the wrong cells.

The first & second Offset Values are not showing at all and the third Offset
is displaying in a cell that is not part of the code.....very odd.....

On a brighter note and from a practical point, I have my origianl code
working, that said, it's not as short or savvy as yours.

Thx again for your efforts.

Cheers
Mick.


Mick
Another mistake on my part...
On the last line in my 'With..End With' construct, remove the dot in
front of Range("AU30") OR prepend the line with the appropriate
sheetname ("RunSheet P1") if that's not the active sheet at runtime.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 259
Default In need of more guidance please....

Thx Garry

Still no joy on this code placing the Values in their correct columns.

Not sure if this has any bearing, but some of the Columns are merged across
as many as 13 Columns due to the nature & structure of the sheet in
question, although, I can't see that being the reason as the code I
originally put together works on them.

I have changed it slightly but it is functional.

Sub Go_Runsheet()

With Application
.ScreenUpdating = False
End With

Sheets("RunSheet P1").Select

Columns("Y").Find("", Cells(Rows.Count, "Y"), xlValues, _
xlWhole, , xlNext).Select

ActiveCell.Select
With Selection
.Value = Sheets("Run Setup").Range("D2").Value
End With
ActiveCell.Offset(0, 55).Select
With Selection
.Value = Sheets("Run Setup").Range("A2").Value
End With
ActiveCell.Offset(0, 1).Select
With Selection
.Value = Sheets("Run Setup").Range("B2").Value
End With
ActiveCell.Offset(0, 1).Select
With Selection
.Value = Sheets("Run Setup").Range("F2").Value
End With

Range("AU30").Value = Sheets("Run Setup").Range("E2").Value

With Application
.ScreenUpdating = True
End With

End Sub

Thx again.

Mick.


  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default In need of more guidance please....

Vacuum Sealed wrote on 6/20/2011 :
Thx Garry

Still no joy on this code placing the Values in their correct columns.

Not sure if this has any bearing, but some of the Columns are merged across
as many as 13 Columns due to the nature & structure of the sheet in question,
although, I can't see that being the reason as the code I originally put
together works on them.

I have changed it slightly but it is functional.

Sub Go_Runsheet()

With Application
.ScreenUpdating = False
End With

Sheets("RunSheet P1").Select

Columns("Y").Find("", Cells(Rows.Count, "Y"), xlValues, _
xlWhole, , xlNext).Select

ActiveCell.Select
With Selection
.Value = Sheets("Run Setup").Range("D2").Value
End With
ActiveCell.Offset(0, 55).Select
With Selection
.Value = Sheets("Run Setup").Range("A2").Value
End With
ActiveCell.Offset(0, 1).Select
With Selection
.Value = Sheets("Run Setup").Range("B2").Value
End With
ActiveCell.Offset(0, 1).Select
With Selection
.Value = Sheets("Run Setup").Range("F2").Value
End With

Range("AU30").Value = Sheets("Run Setup").Range("E2").Value

With Application
.ScreenUpdating = True
End With

End Sub

Thx again.

Mick.


My code works for my sample sheets, so I don't understand why it
doesn't work for you. (It's does exactly the same thing as yours does,
just more efficiently!)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 259
Default In need of more guidance please....

Thx Garry

Don't stress to much over, you have helped me so much already....

Cheers
Mick.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Guidance required please... Vacuum Sealed Excel Programming 19 June 20th 11 11:32 AM
statistics - guidance Duke Carey Excel Worksheet Functions 2 November 6th 08 10:46 PM
Need some guidance Buxton Excel Worksheet Functions 1 December 25th 07 10:11 PM
Implementation Guidance [email protected] Excel Programming 1 July 3rd 07 02:06 AM
looking for logic guidance Bruce Excel Programming 1 February 17th 07 04:40 AM


All times are GMT +1. The time now is 04:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"