Hidden values in cell

[pukiamak]

Hello guys,

I have supposedly empty cell, where I have used Replace function to replace some of data. When i try to check if it's empty, its returning false. How do i check hidden values in cell?

Thx,
Tony

 

[SkipVought]

What was in the cell? Literal, formula?

When you EDIT the cell (F2) what do you see in the Formula Bar?

Can you move the cursor with the Left & Right Arrow Keys?

Skip,
[sub]
[red][/red]
[/sub]

 

[pukiamak]

Hello SkipVought,

Thanks for the reply. Before I did the replace, inside the cells are all string data. Here is what found after i press F2 key to the replaced cell. It blinks in 3rd space from the left not from the left most of the cell. This should have tell me something but I do not know. Do you know how to make it to the left most?

Thx,
Tony

 

[PHV]

So, the cell is not empty but contains spaces ?
How did you use the Replace function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pukiamak]

Hello PHV,

Yeah it contains some space. Well actually i wrote replace macro in which it commpares the content of the cells in three sheets. after i run the macro, the replaced cell have spaces. Any idea?

Thx,
Tony

 

[PHV]

after i run the macro
Which macro ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pukiamak]

its macro that i wrote. well, do you know how to make the replaced cell start from the leftmost of the cell? i try to use left indent but it does not work.

thx,
Tony

 

[PHV]

As you refuse to post your code, just a guess:
use the Trim function in conjunction with the Replace function ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pukiamak]

Hello Phv,

Well, here is my code.


Sub Macro1()
Dim row As Integer
Dim shtrow As Integer

row = 2

Dim working_dog_str As String
Dim working_paul_str As String
Dim working_morlet_str As String
Dim working_haub_str As String
Dim working_daub_str As String

Dim working_dog_str2 As String
Dim working_paul_str2 As String
Dim working_morlet_str2 As String
Dim working_haub_str2 As String
Dim working_daub_str2 As String
Dim temp As String

Do While Sheets(1).Range("A1").Cells(row, 1).Value <> ""
row1_variable = Sheets(1).Range("A1").Cells(row, 1).Value

working_dog_str = Sheets(1).Range("A1").Cells(row, 2).Value
working_paul_str = Sheets(1).Range("A1").Cells(row, 3).Value
working_morlet_str = Sheets(1).Range("A1").Cells(row, 4).Value
working_haub_str = Sheets(1).Range("A1").Cells(row, 5).Value
working_daub_str = Sheets(1).Range("A1").Cells(row, 6).Value


For sht = 2 To 3
shtrow = 2
Do While Sheets(sht).Range("A1").Cells(shtrow, 1).Value <> ""
working_dog_str2 = Sheets(sht).Range("A1").Cells(shtrow, 2).Value
working_paul_str2 = Sheets(sht).Range("A1").Cells(shtrow, 3).Value
working_morlet_str2 = Sheets(sht).Range("A1").Cells(shtrow, 4).Value
working_haub_str2 = Sheets(sht).Range("A1").Cells(shtrow, 5).Value
working_daub_str2 = Sheets(sht).Range("A1").Cells(shtrow, 6).Value
'temp = Sheets(sht).Range("A1").Cells(shtrow, 1).Value = row1_variable

If (row1_variable = Sheets(sht).Cells(shtrow, 1).Value) Then

'------------------------------------------DOG-----------------------------------------
' If InStr(working_dog_str, working_dog_str2) <> 0 Then

If InStr(1, working_dog_str, "(D,+,H)") And InStr(working_dog_str2, "(D,+,H)") Then
Sheets(1).Cells(row, 2).Replace What:="(D,+,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(1, working_dog_str, "(D,-,H)") And InStr(working_dog_str2, "(D,-,H)") Then
Sheets(1).Cells(row, 2).Replace What:="(D,-,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(1, working_dog_str, "(D,+,M)") And InStr(working_dog_str2, "(D,+,M)") Then
Sheets(1).Cells(row, 2).Replace What:="(D,+,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(1, working_dog_str, "(D,-,M)") And InStr(working_dog_str2, "(D,-,M)") Then
Sheets(1).Cells(row, 2).Replace What:="(D,-,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(1, working_dog_str, "(D,+,L)") And InStr(working_dog_str2, "(D,+,L)") Then
Sheets(1).Cells(row, 2).Replace What:="(D,+,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(1, working_dog_str, "(D,-,L)") And InStr(working_dog_str2, "(D,-,L)") Then
Sheets(1).Cells(row, 2).Replace What:="(D,-,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If



'End If
'-------------------------------Paul------------------------------------------------------
' If InStr(working_paul_str, working_paul_str2) <> 0 Then
If InStr(working_paul_str, "(P,+,H)") And InStr(working_paul_str2, "(P,+,H)") Then
Sheets(1).Cells(row, 3).Replace What:="(P,+,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_paul_str, "(P,-,H)") And InStr(working_paul_str2, "(P,-,H)") Then
Sheets(1).Cells(row, 3).Replace What:="(P,-,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_paul_str, "(P,+,H)") And InStr(working_paul_str2, "(P,+,H)") Then
Sheets(1).Cells(row, 3).Replace What:="(P,+,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_paul_str, "(P,+,M)") And InStr(working_paul_str2, "(P,+,M)") Then
Sheets(1).Cells(row, 3).Replace What:="(P,+,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_paul_str, "(P,-,M)") And InStr(working_paul_str2, "(P,-,M)") Then
Sheets(1).Cells(row, 3).Replace What:="(P,-,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_paul_str, "(P,+,L)") And InStr(working_paul_str2, "(P,+,L)") Then
Sheets(1).Cells(row, 3).Replace What:="(P,+,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_paul_str, "(P,-,L)") And InStr(working_paul_str2, "(P,-,L)") Then
Sheets(1).Cells(row, 3).Replace What:="(P,-,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
' End If
'-----------------------------------------Morlet--------------------------------------------
' If InStr(working_morlet_str, working_morlet_str2) <> 0 Then

If InStr(working_morlet_str, "(M,+,H)") And InStr(working_morlet_str2, "(M,+,H)") Then
Sheets(1).Cells(row, 4).Replace What:="(M,+,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_morlet_str, "(M,-,H)") And InStr(working_morlet_str2, "(M,-,H)") Then
Sheets(1).Cells(row, 4).Replace What:="(M,-,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_morlet_str, "(M,+,M)") And InStr(working_morlet_str2, "(M,+,M)") Then
Sheets(1).Cells(row, 4).Replace What:="(M,+,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_morlet_str, "(M,-,M)") And InStr(working_morlet_str2, "(M,-,M)") Then
Sheets(1).Cells(row, 4).Replace What:="(M,-,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_morlet_str, "(M,+,L)") And InStr(working_morlet_str2, "(M,+,L)") Then
Sheets(1).Cells(row, 4).Replace What:="(M,+,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_morlet_str, "(M,-,L)") And InStr(working_morlet_str2, "(M,-,L)") Then
Sheets(1).Cells(row, 4).Replace What:="(M,-,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
' End If
'-----------------------------------Haub--------------------------------------------------
' If InStr(working_haub_str, working_haub_str2) <> 0 Then
If InStr(working_haub_str, "(H,+,H)") And InStr(working_haub_str2, "(H,+,H)") Then
Sheets(1).Cells(row, 5).Replace What:="(H,+,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_haub_str, "(H,-,H)") And InStr(working_haub_str2, "(H,-,H)") Then
Sheets(1).Cells(row, 5).Replace What:="(H,-,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_haub_str, "(H,-,L)") And InStr(working_haub_str2, "(H,-,L)") Then
Sheets(1).Cells(row, 5).Replace What:="(H,-,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_haub_str, "(H,+,M)") And InStr(working_haub_str2, "(H,+,M)") Then
Sheets(1).Cells(row, 5).Replace What:="(H,+,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_haub_str, "(H,-,M)") And InStr(working_haub_str2, "(H,-,M)") Then
Sheets(1).Cells(row, 5).Replace What:="(H,-,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_haub_str, "(H,+,L)") And InStr(working_haub_str2, "(H,+,L)") Then
Sheets(1).Cells(row, 5).Replace What:="(H,+,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

' End If
'--------------------------------Daub-----------------------------------------------------
' If InStr(working_daub_str, working_daub_str2) <> 0 Then
If InStr(working_daub_str, "(Daub,+,H)") And InStr(working_daub_str2, "(Daub,+,H)") Then
Sheets(1).Cells(row, 6).Replace What:="(Daub,+,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_daub_str, "(Daub,-,H)") And InStr(working_daub_str2, "(Daub,-,H)") Then
Sheets(1).Cells(row, 6).Replace What:="(Daub,-,H),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_daub_str, "(Daub,+,M)") And InStr(working_daub_str2, "(Daub,+,M)") Then
Sheets(1).Cells(row, 6).Replace What:="(Daub,+,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_daub_str, "(Daub,-,M)") And InStr(working_daub_str2, "(Daub,-,M)") Then
Sheets(1).Cells(row, 6).Replace What:="(Daub,-,M),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_daub_str, "(Daub,+,L)") And InStr(working_daub_str2, "(Daub,+,L)") Then
Sheets(1).Cells(row, 6).Replace What:="(Daub,+,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

If InStr(working_daub_str, "(Daub,-,L)") And InStr(working_daub_str2, "(Daub,-,L)") Then
Sheets(1).Cells(row, 6).Replace What:="(Daub,-,L),", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

'End If
End If

shtrow = shtrow + 1
Loop
Next sht

row = row + 1
Loop

End Sub


I will try to use trim function to remove the space.

Thx,
TOny