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...?

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

On Sunday, June 23, 2013 2:22:56 AM UTC-7, Claus Busch wrote:
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...?



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

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

On Sunday, June 23, 2013 2:22:56 AM UTC-7, Claus Busch wrote:
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...?



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

This returns true:

=J26='Specified Sheet Name'!G6

Hi Howard,

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

This returns true:

=J26='Specified Sheet Name'!G6

for me it works fine. You can load down the workbook "Match" from:
https://skydrive.live.com/#cid=9378A...121822A3%21326


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

"Howard" wrote:
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

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").

On Sunday, June 23, 2013 3:23:15 AM UTC-7, Claus Busch wrote:
Hi Howard,



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



This returns true:



=J26='Specified Sheet Name'!G6



for me it works fine. You can load down the workbook "Match" from:

https://skydrive.live.com/#cid=9378A...121822A3%21326





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,
The code you offer works excellent, now that I have put it in a standard module.

Thanks, I appreciate your help.

Regards,
Howard

On Sunday, June 23, 2013 7:45:03 AM UTC-7, joeu2004 wrote:
"Howard" wrote:

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



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").

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