In need of more guidance please....

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

Vacuum Sealed

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.
 
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
 
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...
Click to expand...

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
Click to expand...

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
 
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.
 
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.
 
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.
Click to expand...

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
 
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.
 
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.
Click to expand...

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.
 
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.
 
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.
Click to expand...

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!)
 
Thx Garry

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

Cheers
Mick.
 
Back
Top