Target.value 255 explanation

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
Hi there!

I was given the following line as part of a sub:
Code:
FindWhat = Left(Target.Value, 255)
I need to understand what this actually does.

I was wanting a match to be found by looking through all text entries with no limit to the number of characters in the cell and I suspect the above line is incorrect.

At the moment my sheet is being pasted with rogue text entries that are all greater than 255 characters, and I think it has something to do with this line :cry:

If you need me to post more I will - hope you can help!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Hap

Well-known Member
Joined
Jul 20, 2005
Messages
647
The line is starting from the last column and moving left through the columns up to the Target.Value. (255 is reference to a maximum number of columns in a sheet)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hap said:
The line is starting from the last column and moving left through the columns up to the Target.Value. (255 is reference to a maximum number of columns in a sheet)
Sorry, but this is incorrect.
The line of code above is saying to find the first 255 characters (from the left end) in the target cell's value.

(Also, there are (currently) 256 columns available.)

I'm afraid we'll need to see more of your code (or at least I do...) to hopefully help on the rogue pasting issue.

Hope it helps.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
255 is the max column width of a cell.

See the topic Worksheet and workbook specifications in the help file.
 

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725

ADVERTISEMENT

Guys!

Many thanks for your lightning responses!

This is the full code:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 6 Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Application.ScreenUpdating = False
Dim FindWhat$, FindWhere As Variant
FindWhat = Left(Target.Value, 255) 'THIS ROW IS THE POSSIBLE PROBLEM
Set FindWhere = _
Sheets("Training Log").Columns(7).Find(What:=FindWhat, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=True)
If FindWhere Is Nothing Then Exit Sub

Dim iIndex%
iIndex = Sheets("Training Log").Cells(FindWhere.Row, 6).Interior.ColorIndex
Target.Hyperlinks.Add _
Anchor:=Target, _
Address:="", _
SubAddress:="'Training Log'!G" & FindWhere.Row, _
TextToDisplay:="Click for Training Log Comments", _
ScreenTip:="Go to Training Log G" & FindWhere.Row
Target.Interior.ColorIndex = iIndex
With Target
.Font.FontStyle = Arial
.Font.Size = 8
.Font.Bold = True
Application.ScreenUpdating = True
End With
End Sub

What this code is doing is searching for duplicated text in column G of Training Log and Column F of the sheet the code appears in ('Analysis') creating a hyperlink to it in Column F of Analysis. What's also happening is the newly-hyperlinked cell is also shaded the same colour as column F adjacent to the duplicated text cell in Training Log e.g.

Analysis F234 duplicate text located to Training Log G20
Training Log F20 filled green
Hyperlink created in Analysis F234, cell filled green

Hope you can help - and thanks either way!
 

Hap

Well-known Member
Joined
Jul 20, 2005
Messages
647
Thank you for correcting me. I had a brain fart and was remembering a similar line of code I wrote earlier.
 

Hap

Well-known Member
Joined
Jul 20, 2005
Messages
647

ADVERTISEMENT

Now that I made a fool of myself I have a related question. Is the function just containing the string so that it can be assigned to a cell without accidently oversizing it (since Target.Value in the example above could potentially be larger than 255 characters)?
 

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
Hap - thanks for your help!

Well what I'm wanting Excel to do is to look through all the entries in Column G of Training Log and hyperlink to that cell from Analysis sheet if an exact match is found - I'm not sure of the details but before I added the ", 255" to that line, I was getting a Type Mismatch error.

I looked at all the occasions when I was getting this error, and it was when Excel had located the match but there were more than 255 characters in the cell.

I had thought that my code had resolved it - the macro seems to work fine, but for some reason I'm getting seemingly random entries in column G Training Log replaced by 1 of 2 entries that both contain well over 255 characters - so I'm thinking the 2 things are somehow linked...?

Hope that's a bit clearer?!
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hap:
:biggrin: You're a good sport. I still get corrected here myself. (Thankfully, not as much as I used to, but still...) :rolleyes:
Wish I could answer your question directly but we'll need someone better than me for that. All I know is that the Find function doesn't seem to like finding strings of more than 255 characters so the limit is necessary.

zoso:
I'm using your code in a test wb and can't seem to duplicate the problem you're having. When I have it find cells with strings of more than 255 it appears to be working as desired. When you say you're "getting seemingly random entries in column G Training Log replaced by 1 of 2 entries that both contain well over 255 characters"... what is random, the cells that get replaced, or the string(s) they're getting replaced with? (Are you seeing any sort of pattern to what's happening?) - So far for me it seems to be working great.
 

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
Hi Halface - thanks ever such a lot for your time trying to help me out!

Yes, I agree, I thought it worked brilliantly too, and it still does. I'd discovered all the pasted entries when I ran a search on a certain word which returned a load of duplicated entries, all over 255 characters.

My original question related to the meaning of the ", 255" - I wanted it to be so it didn't error when a cell containing >255 characters was found. I was thinking maybe that that part of the line had caused the phantom pasted entries, but it seems not.

You asked what is random, the cells that get replaced, or the string(s) they're getting replaced with, and am I seeing any sort of pattern to what's happening?

The cells that get replaced are either in column B or F. There is just 1 text entry being pasted in either of these columns, and it's been repeated 6 times in less than a week, but I can't see any pattern. If I'd identified that I could have been more specific of course.

Anyway, I hope that's of some use - if you like I'm happy to send you the file...

TIA!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,697
Members
412,481
Latest member
nhantam
Top