Trouble with returning Values

[bloodmilksky]

Hi Guys,

I am having trouble with the below code. it basically returns a range dependent on the users name going into cell B7.

I have amended the amount of information it returns (HL Below) and it is still returning the same range prior to me changing it.

I was just wondering if anycould help me please?

many thanks

jamie

LastRowMenu = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row + 1
LastRow = Sheets("Sheet8").Cells(Rows.Count, "B").End(xlUp).Row
If Not Intersect(Target, Range("B7")) Is Nothing Then
If Target.Value <> "" Then
FindString = UCase(Target.Value)
Set sRange = Sheets("Sheet8").UsedRange
For Each Cell In sRange
If InStr(1, UCase(Cell.Value), FindString) Then
[COLOR=#ff0000][B]Sheets("Sheet8").Range(Cell, Cell.Offset(34, 4)).Copy[/B][/COLOR]
Sheets("Sheet1").Range("B" & LastRowMenu).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Draw in the gridlines
With Selection.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
End With
Selection.BorderAround Weight:=xlMedium
' Increase LastRowMenu by 8 to account for the new data
LastRowMenu = LastRowMenu + 8
End If
Next Cell..
If Range("F2:I36") = "" Then
' Display an error stating the name is not in the list
MsgBox "Specified name does not exist", vbOKOnly, "Attention!"
' Clear the contents of A1
Target.ClearContents
' Reselect cell A1
Range("B7").Select
End If
Else
' Clear the contents of Lenses section on the Menu sheet
Range("F2:I36" & LastRowMenu).ClearContents
End If
End If


End Sub

 

[StephenCrump]

At present, your code loops through every Cell in Sheets("Sheet8").UsedRange, and every time your InStr() function finds a match, it copies 35 rows and 5 columns (with Cell as the top left cell) to the end of Column B in Sheet 1.

Your code then increments the row counter for Sheet1 by only 8, so subsequent copy/pastes will overwrite each other to some extent.

What's not clear is what you want your code to do?

 

[bloodmilksky]

Hi,

So the when the user opens up this workbook their username appears in Cell B7 this code then searches for their user name and returns their profile on sheet 8 which is 35x5. but the problem is that I previously had it set up for 35x3 and even though I have amended the code that is all that is being returned.

 

[StephenCrump]

So the when the user opens up this workbook their username appears in Cell B7 this code then searches for their user name and returns their profile on sheet 8 which is 35x5. but the problem is that I previously had it set up for 35x3 and even though I have amended the code that is all that is being returned.

This line definitely copies a range of 35 rows and 5 columns:

Sheets("Sheet8").Range(Cell, Cell.Offset(34, 4)).Copy

So something else is happening that you're not expecting ...

Can we assume that your code is in a Sub Worksheet_Change? If so, for which worksheet?

Will the Username be found only once? Do we need to look only in a particular column? If yes, we don't need to loop, and we certainly won't need to loop through every cell in the UsedRange.