Value from one sheet paste under same value on other sheet.

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

Trying to take the value in J26 sheet1 and paste it under the same value on sheet "Specified Sheet Name" which will be in the named range SSname C6:Z6.

The J26 value will look something like this SS-Name 10.

I get no errors on this code and no results either...?

Thanks,
Howard

Option Explicit

Sub SSName_Match_Offset()
Dim sNme As String
Dim SSName As Range
Dim c As Range

With Sheets("Specified Sheet Name")
sNme = Sheets("Sheet1").Range("J26").Value
Set SSName = Range("C6:Z6")

For Each c In SSName ' Range("C6:Z6") I tried the actual range also

If c.Value = sNme Then
c.Offset(1, 0) = sNme
End If

Next
End With
End Sub
 
Hi Howard,

Am Sun, 23 Jun 2013 02:02:06 -0700 (PDT) schrieb Howard:
Trying to take the value in J26 sheet1 and paste it under the same value on sheet "Specified Sheet Name" which will be in the named range SSname C6:Z6.

The J26 value will look something like this SS-Name 10.

I get no errors on this code and no results either...?
Click to expand...

try:
Sub SSName_Match_Offset()
Dim sNme As String
Dim SSName As Range
Dim c As Range

With Sheets("Specified Sheet Name")
sNme = Sheets("Sheet1").Range("J26").Value
For Each c In Range("SSName")
If c.Value = sNme Then
c.Offset(1, 0) = sNme
Exit For
End If
Next
End With
End Sub

If you get no result, in J26 and in SSName the spelling is different


Regards
Claus Busch
 
Hi Howard,



Am Sun, 23 Jun 2013 02:02:06 -0700 (PDT) schrieb Howard:






try:

Sub SSName_Match_Offset()

Dim sNme As String

Dim SSName As Range

Dim c As Range



With Sheets("Specified Sheet Name")

sNme = Sheets("Sheet1").Range("J26").Value

For Each c In Range("SSName")

If c.Value = sNme Then

c.Offset(1, 0) = sNme

Exit For

End If

Next

End With

End Sub



If you get no result, in J26 and in SSName the spelling is different





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

HI Claus,

If I run your corrected code from a forms button I get the 400 error box in the vb editor.
If I run the code from the vb editor then I get the Application-defined or Object-defined error.

I copied J26 and pasted it in the range to be certain of a true match. Both before with my old non responsive code and again with yours.

Howard
 
Hi Howard,



Am Sun, 23 Jun 2013 02:02:06 -0700 (PDT) schrieb Howard:






try:

Sub SSName_Match_Offset()

Dim sNme As String

Dim SSName As Range

Dim c As Range



With Sheets("Specified Sheet Name")

sNme = Sheets("Sheet1").Range("J26").Value

For Each c In Range("SSName")

If c.Value = sNme Then

c.Offset(1, 0) = sNme

Exit For

End If

Next

End With

End Sub



If you get no result, in J26 and in SSName the spelling is different





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

This returns true:

=J26='Specified Sheet Name'!G6
 
Howard said:
Trying to take the value in J26 sheet1 and paste it under
the same value on sheet "Specified Sheet Name" which will
be in the named range SSname C6:Z6. [....]
I get no errors on this code and no results either...? [....]
Sub SSName_Match_Offset()
Dim sNme As String
Dim SSName As Range
Dim c As Range
With Sheets("Specified Sheet Name")
sNme = Sheets("Sheet1").Range("J26").Value
Set SSName = Range("C6:Z6")
For Each c In SSName ' Range("C6:Z6") I tried the actual range also
If c.Value = sNme Then
c.Offset(1, 0) = sNme
End If
Next
End With
End Sub
Click to expand...

You probably want:

Set SSName = .Range("C6:Z6")

Note the dot before "Range". Alternatively, I prefer to dispense with the
With statement and write:

Set SSName = Sheets("Specified Sheet Name").Range("C6:Z6")

If the scope of the defined name "SSname" is Workbook, the following should
work even without the leading dot and even within the With statement:

Set SSName = Range("SSname")

However, if the scope is a worksheet, you will need the leading dot, to wit:

Set SSName = .Range("SSname")

Again, I would prefer Sheets("Specified Sheet Name").Range("SSname").
 
Howard said:
Trying to take the value in J26 sheet1 and paste it under
Click to expand...
the same value on sheet "Specified Sheet Name" which will
Click to expand...
be in the named range SSname C6:Z6.
[....]

I get no errors on this code and no results either...?
[....]

Sub SSName_Match_Offset()
Click to expand...
Dim sNme As String
Click to expand...
Dim SSName As Range
Click to expand...
Dim c As Range
Click to expand...
With Sheets("Specified Sheet Name")
Click to expand...
sNme = Sheets("Sheet1").Range("J26").Value
Click to expand...
Set SSName = Range("C6:Z6")
Click to expand...
For Each c In SSName ' Range("C6:Z6") I tried the actual range also
Click to expand...
If c.Value = sNme Then
Click to expand...
c.Offset(1, 0) = sNme
Click to expand...
End If

End With
Click to expand...
End Sub
Click to expand...



You probably want:



Set SSName = .Range("C6:Z6")



Note the dot before "Range". Alternatively, I prefer to dispense with the

With statement and write:



Set SSName = Sheets("Specified Sheet Name").Range("C6:Z6")



If the scope of the defined name "SSname" is Workbook, the following should

work even without the leading dot and even within the With statement:



Set SSName = Range("SSname")



However, if the scope is a worksheet, you will need the leading dot, to wit:



Set SSName = .Range("SSname")



Again, I would prefer Sheets("Specified Sheet Name").Range("SSname").
Click to expand...

Hi joeu2004,

As I posted to Claus, it works fine in a standard module. I wonder if I will ever learn when to use the standard module vs the sheet module.

Clearly, my bad, (along with the bit of code tweeking) but it is fine now.

Thanks for weighing in and the addition info.

I believe I fall into that category of "...being able to speak well and gooder English" but have trouble writing it.<G>

Regards,
Howard
 
Back
Top