determain the column

[limeg95]

Hi!
i have excel form 8 to 40 rows
find the specific column which put tick mark when it has left column has numbers with screen, if it matches put tick mark in that specific oolumn
for exampl Mar.2 mar4, mar.12 there are three column
find mar.12 column put tick mark if the mar.4 has number with screen value or if the mar.4 has tick mark check with mar.2 with screen value, if it matches put tick mark in mar.12 if it differes the screen value put in mar.12
i have code. but still stuggling how to find mar.2 or mar.4 has numbers.
here is the code
--------------------------------

ith Worksheets("Sheet1").Range("F7:I7")
Myday = Format((Myday), "mmm.d")
Set c = .Find(Myday, LookIn:=xlValues)
If Not c Is Nothing Then
MyCl = c.Column
If Cells(x, MyCl).Text <> "" Then '' check to see if data exist if it does go to next date
Do
c.Value = Myday
Set c = .FindNext(c)
If Cells(x, c.Column).Text = "" Then
MyCl = c.Column
Exit Do
End If
Loop While Not c Is Nothing And MyCol <> c.Column
End If
End If
End With
MyScreen.WaitHostQuiet (g_HostSettleTimE)
' MyScreen.PutString "s", x, 29

Do
If x > 40 Then Exit Do
For j = 8 To 23
MyScreen.PutString "s", j, 29

MyScreen.SendKeys ("<ENTER>")
MyScreen.Moveto 2, 40

MyScreen.SendKeys ("b")
MyScreen.SendKeys ("<ENTER>")
MyScreen.WaitHostQuiet (g_HostSettleTimE)
If MyScreen.GetString(1, 59, 8) = "OMC9979" Then

Set MYArea = MyScreen.Search("DEFINED:")

If MYArea = "DEFINED:" Then

MyScreen.Moveto MYArea.Bottom, MYArea.Left

MYRw = MyScreen.Row

MyCol = MyScreen.Col

Mytotal1 = Trim(MyScreen.GetString(MYRw, (MyCol + 8), 70))
Mytotal2 = Cells(x, (MyCl - 1)).Value

MyScreen.WaitHostQuiet (g_HostSettleTimE)
Mytotal3 = Cells(x, (MyCl - 2)).Text
Mytotal4 = Cells(x, (MyCl - 3)).Text
If Cells(x, (MyCl - 1)).Value = "ü" Then
If Trim(Mytotal3) = Mytotal1 Then

Cells(x, (MyCl)).Value = "ü"
Cells(x, (MyCl)).Characters.Font.Name = "Wingdings"
Else
Cells(x, (MyCl)).Value = Mytotal1
End If
Else
If Mytotal1 = Trim(Mytotal3) Then
Cells(x, (MyCl)).Value = "ü"
Cells(x, (MyCl)).Characters.Font.Name = "Wingdings"
Else
Cells(x, (MyCl)).Value = Mytotal1
End If
End If

 

[SkipVought]

Hi,

Please post a cogent example of data in these columns and rows, by pasting a range from your sheet.

Explain your logic, using the example and give the expected results.

Please! When you post your code, post complete segments, matching the start and END of all code structures, like For...Next and Do...Loop and If...Else...End if. Otherwise, it will be frustrating for you and me!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[limeg95]

skip
Apologies
Feb.12 mar.2 mar5 mar12
40 v v
50 v'tickmark) v 50 v
120 150(not match with feb.12 to screen)
60 v

for example user enters Mar.2 macro goes to mar,2 column
check left column the number 40 with screen Mytotal1 = Trim(MyScreen.GetString(MYRw, (MyCol + 8), 70))
it matches so it put tick mark
and go to next row check with screen and feb.12
if the user enters mar.2 it need to check left most column
if it s number that number checks with screen
if it matches it goes to tick mark
---------------

Set System = CreateObject("EXTRA.System")
Set Sess = System.ActiveSession
Set MyScreen = Sess.Screen
Set appExcel = GetObject(, "Excel.Application")
If appExcel Is Nothing Then
' If GetObject failed, open a new instance of Excel 97
Set appExcel = CreateObject("Excel.Application")
If appExcel Is Nothing Then
MsgBox ("Could not open Excel.")
Exit Sub
End If
End If
appExcel.DisplayAlerts = True

Close #1

Set wbExcel = appExcel.ActiveWorkbook
If wbExcel Is Nothing Then
MsgBox ("Could not open Excel workbook.")
appExcel.Quit
Exit Sub
End If


wbExcel.Activate
g_HostSettleTimE = 650 ' milliseconds

Sess.Connected = True
If (Sess Is Nothing) Then
MsgBox "Could not create the Sessions collection object. Stopping macro playback."
Stop
End If

MyScreen.WaitHostQuiet (g_HostSettleTimE)

MyScreen.SendKeys ("<tab>s<Enter>")

MyScreen.SendKeys ("1<Enter>")

MyScreen.Moveto 6, 50
MyScreen.PutString Myday, 6, 48


MyScreen.Moveto 6, 57
MyScreen.PutString "mnsps", 6, 58

MyScreen.Moveto 6, 69

MyScreen.PutString "gdr", 6, 69



MyScreen.SendKeys ("<Enter>")

x = 8


With Worksheets("Sheet1").Range("F7:I7")
Myday = Format((Myday), "mmm.d")
Set c = .Find(Myday, LookIn:=xlValues)
If Not c Is Nothing Then
MyCl = c.Column
If Cells(x, MyCl).Text <> "" Then '' check to see if data exist if it does go to next date
Do
c.Value = Myday
Set c = .FindNext(c)
If Cells(x, c.Column).Text = "" Then
MyCl = c.Column
Exit Do
End If
Loop While Not c Is Nothing And MyCol <> c.Column
End If
End If
End With
MyScreen.WaitHostQuiet (g_HostSettleTimE)
' MyScreen.PutString "s", x, 29

Do
If x > 40 Then Exit Do
For j = 8 To 23
MyScreen.PutString "s", j, 29

MyScreen.SendKeys ("<ENTER>")
MyScreen.Moveto 2, 40

MyScreen.SendKeys ("b")
MyScreen.SendKeys ("<ENTER>")
MyScreen.WaitHostQuiet (g_HostSettleTimE)
If MyScreen.GetString(1, 59, 8) = "ODHY9915" Then

Set MYArea = MyScreen.Search("DEFINED:")

If MYArea = "DEFINED:" Then

MyScreen.Moveto MYArea.Bottom, MYArea.Left

MYRw = MyScreen.Row

MyCol = MyScreen.Col

Mytotal1 = Trim(MyScreen.GetString(MYRw, (MyCol + 8), 70))
Mytotal2 = Cells(x, (MyCl - 1)).Value

MyScreen.WaitHostQuiet (g_HostSettleTimE)
Mytotal3 = Cells(x, (MyCl - 2)).Text
Mytotal4 = Cells(x, (MyCl - 3)).Text
If Cells(x, (MyCl - 1)).Value = "ü" Then
If Trim(Mytotal3) = Mytotal1 Then

Cells(x, (MyCl)).Value = "ü"
Cells(x, (MyCl)).Characters.Font.Name = "Wingdings"
Else
Cells(x, (MyCl)).Value = Mytotal1
End If
Else
If Mytotal1 = Trim(Mytotal3) Then
Cells(x, (MyCl)).Value = "ü"
Cells(x, (MyCl)).Characters.Font.Name = "Wingdings"
Else
Cells(x, (MyCl)).Value = Mytotal1
End If
End If

MyScreen.SendKeys ("<pf3>")



x = x + 1


End If
Next j

Loop Until x > 40
MyScreen.SendKeys ("<PF3>")

End Sub

 

[limeg95]

hi Skip
could you help me

 

[limeg95]

[col1] [col2] col3 col4
Feb.12 Mar.2 Mar.5 mar.12
88 v v v ' v means tick mark
55 v 75 v
45 v v v
100 v 200 v
if i find column Mar.12 how do i check feb.12 and screen
screen part i can find
but excel part how do i find feb.12
mar.12 cehck with previous column that mar.5 is tick mark
go to previous column that is also tick mark it goes to another column that is feb. 12 cehck with screen
if it matches put tick mark in mar.12 or wahtever the screen value put it in mar.12