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


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
 
Ad

Advertisements

C

Claus Busch

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
 
H

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

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
 
H

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

This returns true:

=J26='Specified Sheet Name'!G6
 
J

joeu2004

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

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

Advertisements

Ad

Advertisements

H

Howard

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

End With



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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top