Can somebody help with this code? - I added the code this time

[earp_]

Hi, i've tried to find a way to fix things by myself but it looks I have no hope right now.

In the xls file you can find 4 sheets.
Sheet1A and Sheet1B
(Sheet2A and Sheet2B identical to the others except for the values)

In Sheet1A i have some columns which have values into the rows.
In sheet1B I have a button, when I press the button, if the time near the button is found in column A of Sheet1B then I add the first value, the max value, the min value and the last value.

You can try to change the time value to add other values to the column B,C,D and E of Sheet1B

My problem: it doesn't work. Sometimes it works if I pressed once, but after it puts 0.00 for the max and it seems to have another problems.

I can change the structure of my macro I don't mind.

Can you please tell me how to make a clear and simple code which works?
tks.

A friend of mine gave me the possibility to put the code here
http://www.racoonlab.com/test.zip

 

[earp_]

Also because I put into the code what I asked in the previous posts...so should work

 

[Smitty]

How about just posting the code that you have now?

Even if I could access an internet storage site, I probably wouldn't open an untrusted file.

 

[earp_]

how? it's a long code
do you want me to put my code on QUOTE?

 

[Smitty]

Just post the relevant portion(s) of it.

You can post code using code tags:

[ code ] Your code here [ / code ]

(Remove the spaces between the text and the brackets).

 

[earp_]

Option Explicit

Dim IsEqualSheet1 As Boolean
Dim tmpMinuteCheckSheet1 As Integer
Dim tmpMinuteSheet1 As Integer


Sub myMacro1()

Dim wbSheet1 As Workbook
Set wbSheet1 = Workbooks("test.xls")
Dim colSheet2Sheet1 As String
Dim colPrevSheet2Sheet1 As String
Dim pIndexSheet1 As String
Dim numRowSheet1 As Integer

Dim cRangeSheet1 As Range
Dim timeMSheet1 As Range
Dim cHourSheet1 As Integer
Dim nHourSheet1 As Integer
Dim cMinuteSheet1 As Integer
Dim nMinuteSheet1 As Integer
Dim myHourSheet1 As Integer
Dim myMinuteSheet1 As Integer
Dim myVarSheet1 As Integer
Dim myColCheckSheet1 As Integer
Dim myTimeToCheckSheet1 As String
Dim CellSheet1 As Range
Dim colSheet1 As Integer
Dim RngSheet1 As Range


Set timeMSheet1 = wbSheet1.Worksheets("mySheet1B").Range("K14")
'On Error Resume Next

'check #N/A Sheet1 J2
If timeMSheet1.Text <> "#N/A" Then
    
    
    myHourSheet1 = Hour(timeMSheet1)
    myMinuteSheet1 = Minute(timeMSheet1)

'#######################
'change the tima value
    '15.30
    
    If (myHourSheet1 = 15) Then
        '15.30 - 15.35 A65536
        If ((myMinuteSheet1 >= 30) And (myMinuteSheet1 < 35)) Then
            colSheet2Sheet1 = "A65536"
            colPrevSheet2Sheet1 = "CH65536"
            pIndexSheet1 = "CH"
            cHourSheet1 = 15
            nHourSheet1 = 15
            cMinuteSheet1 = 30
            nMinuteSheet1 = 35
        End If
        '15.35 - 15.40 B65536
        If ((myMinuteSheet1 >= 35) And (myMinuteSheet1 < 40)) Then
            colSheet2Sheet1 = "B65536"
            colPrevSheet2Sheet1 = "A65536"
            pIndexSheet1 = "A"
            cHourSheet1 = 15
            nHourSheet1 = 15
            cMinuteSheet1 = 35
            nMinuteSheet1 = 40
        End If
        '15.40 - 15.45 C65536
        If ((myMinuteSheet1 >= 40) And (myMinuteSheet1 < 45)) Then
            colSheet2Sheet1 = "C65536"
            colPrevSheet2Sheet1 = "B65536"
            pIndexSheet1 = "B"
            cHourSheet1 = 15
            nHourSheet1 = 15
            cMinuteSheet1 = 40
            nMinuteSheet1 = 45
        End If
        '15.45 - 15.50 D65536
        If ((myMinuteSheet1 >= 45) And (myMinuteSheet1 < 50)) Then
            colSheet2Sheet1 = "D65536"
            colPrevSheet2Sheet1 = "C65536"
            pIndexSheet1 = "C"
            cHourSheet1 = 15
            nHourSheet1 = 15
            cMinuteSheet1 = 45
            nMinuteSheet1 = 50
        End If
    End If
    
 
    
        If timeMSheet1.Value >= TimeSerial(cHourSheet1, cMinuteSheet1, 0) And timeMSheet1.Value < TimeSerial(nHourSheet1, nMinuteSheet1, 0) Then
            
                    
            '####################
            'Count previous column

            
                myTimeToCheckSheet1 = Format(TimeSerial(cHourSheet1, cMinuteSheet1, 0), "hh:mm:ss")
                For Each CellSheet1 In wbSheet1.Worksheets("mySheet1B").Range("A1:A112")
                   If Format(CellSheet1.Value, "hh:mm:ss") = myTimeToCheckSheet1 Then
                     myColCheckSheet1 = CellSheet1.Row
                     Exit For
                   End If
                Next
                
                 Set cRangeSheet1 = wbSheet1.Worksheets("mySheet1A").Range(pIndexSheet1 & "2", wbSheet1.Worksheets("mySheet1A").Range(pIndexSheet1 & "65536").End(xlUp))
                    
                     'first
                    wbSheet1.Worksheets("mySheet1B").Range("B" & myColCheckSheet1).Value = wbSheet1.Worksheets("mySheet1A").Range(pIndexSheet1 & "2").Value
                    'max
                    wbSheet1.Worksheets("mySheet1B").Range("C" & myColCheckSheet1).Value = "=MAX(" & cRangeSheet1.Address & ")"
                    'min
                    wbSheet1.Worksheets("mySheet1B").Range("C" & myColCheckSheet1).Value = "=MIN(" & cRangeSheet1.Address & ")"
                    'last
                    wbSheet1.Worksheets("mySheet1B").Range("E" & myColCheckSheet1).Value = wbSheet1.Worksheets("mySheet1A").Range(pIndexSheet1 & "1").End(xlDown).Value
        
        End If
        


'close if #N/A
End If

End Sub

4 Sheets: mySheet1A, mySheet1B and mySheet2A, mySheet2B
in mySheet1B there is a button to activate the macro myMacro1.
In mySheet1B there is also K14 that is

<table x:str="" style="border-collapse: collapse; width: 65pt;" border="0" cellpadding="0" cellspacing="0" width="87"><col style="width: 65pt;" width="87"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 65pt;" x:num="0.65277777777777779" align="right" width="87" height="17">15:40:00</td> </tr></table>

and in A1 of Sheet1B there is

<table x:str="" style="border-collapse: collapse; width: 50pt;" border="0" cellpadding="0" cellspacing="0" width="66"><col style="width: 50pt;" width="66"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 50pt;" x:num="0.64583333333333337" align="right" width="66" height="17">15:30:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="0.64930555555555558" align="right" height="17">15:35:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="0.65277777777777779" align="right" height="17">15:40:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="0.65625" align="right" height="17">15:45:00</td> </tr> </tbody></table>

When I press the button I want to calculate the values in mySheet1A (random value) and i will put the results in the column A1 of Sheet1B if I find the correspondence between K14 and A1
There is also an online version but i guess you won't trust the code.
Tks in advance.

 

[Norie]

earp

I did have a look at the file, better run a virus check later.

But I'm afraid the code doesn't make a whole lot of sense and seems rather repetitive.

I honestly think you need to rethink the approach to whatever you are trying to achieve, whatever that is.

 

[earp_]

mmm
I need to calculate the max and min first element and last when I click on the button and put the values in column A1 of Sheet1B, in the row where there is a correspondence between the time near the button with the time on the column A
I will start again..but if someone has any idea or 15 spare minutes can always say how to make that!
tks.

 

[earp_]

why i'm not able to find the max and min with this?

'max
wbSheet1.Worksheets("mySheet1B").Range("C" & myColCheckSheet1).Value = "=MAX(" & cRangeSheet1.Address & ")"
'min
wbSheet1.Worksheets("mySheet1B").Range("C" & myColCheckSheet1).Value = "=MIN(" & cRangeSheet1.Address & ")"

 

[earp_]

I think i've got the point after studying the difference between workbook, worksheets and sheet.
I know where to find you if I need ... tks!