Help For Loop Problem

[pr3ach3r]

Hopefully I can explain this well. What I am trying to do is if they put an x in column Q between rows 3 and 46 and a number in any column between f and l on the same row place that value as a negitave value in BH48. The problem is that it appears that the loop is infinate and will of course memory out... aka blow up... lol... help

Dim col(7) As String, newvalue
col(1) = "F"
col(2) = "G"
col(3) = "H"
col(4) = "I"
col(5) = "J"
col(6) = "K"
col(7) = "L"
For i = 3 To 46
For k = 1 To 6
 
     If Range("Q" & i).Value <> "" And Range(col(k) & i).Value > 0 Then
       Sheets("Monday").Range("BH48").Value = -Range(col(k) & i).Value
        newvalue = -Range(col(k) & i).Value
        MsgBox newvalue
        Else:
        Sheets("Monday").Range("BH48").Value = 0
 
      Exit For
     End If
 
    Next
 
Next

 

[lenze]

Hi and welcome to the board!!
Will the user place the "x'' in Column "Q" 1st, or the Number in F:I ?
Wil there only be ONE row with and "X" and a value in F:I?
Will F:I on a row only have 1 vlalue?
lenze

 

[pr3ach3r]

They could place the x or the number it depends on the thought of the user. More than likey because its a timesheet and the it is dressed of course left to right they would probably put the number first.

Yes only one row with values at a time. Because this is on worksheet change it should check everytime there is a change.

Yes only one value.

 

[pr3ach3r]

Maybe this explains it better:

I have a timesheet with lets say:

TIME ENG TIME ADMIN TIME RESEARCH TIME MUT
8:00 AM 1 X
||
\/
4:00 PM 1 X


This is a scenario that could happen 1 hour is made up at 8 am and 1 hour made up at 4 pm. Now I don't want this counted as over time so i want to subtract it from that total but add it to regular hours.

That is the intent of the code.

 

[lenze]

I'm sorry, but you have lost me!!! Can you post a screenshot or explain exactly what happens and when!
lenze

 

[pr3ach3r]

You will note a 1 in column J8 and and X in Q8 but it is possible that there could be time placed in another column and time frame that would be make up time.

What I would like to do is take that value/values and place it in a cell on the workbook.

 

[pr3ach3r]

Maybe I am still complicating my thought too much.

Basic:


If I put an x in column Q anywhere between row 3 and 46, and a number in any column between F and L in the same row. I want to sum the numbers up, if there are more that one instance of that combo, then place that result in a cell somewhere else on the sheet. You can still use my image to get the idea. The code I have is wrong, I know, but it is on the road just not sure if its the right road....