VBA Code gives inconsistant Answers, Where am I going wrong please

[Mike54]

This may be a bit of a long shot but I'm at my wits end, any help would be much appreciated.

The following line of code works on the first half of my grid, but never on a row greater than row 55 !

I have used F8 to step through my code, if the starting point is on row 55 and it works perfectly, row 56 or greater it fails to include the first time component. Grrrr

When stepping through with F8 there is no difference at all between the line of code selected or the values (input row 55 or 56), incidentally there are 42 F8 presses to get to this line.

ActiveCell.FormulaR1C1 = Format(ActiveCell.Offset.Cells(R - 8, 1).Value, "hh:mm") _
& " " & Range("J" & (ActiveCell.Row)).Value & " Fixed Post " & _
Format(ActiveCell.Offset.Cells(R - 8, 1).Value + 0.0417, "hh:mm")


the Offset referred to is a row of Times 07:00, 07:01, 07:02 etc on row 8
R = ActiveCell.Row
Range J is a list of Staff Names

After this line is processed if the input was higher than row 55 the Time is not included! but the staff name & 1 hour at the end are.

I know this is tough to follow but after many hours I'm really stuck, thanks for reading

Mike

 

[Andrew Poulsom]

Why are you offsetting ActiveCell by ActiveCell.Row - 8? The offset will increase as ActiveCell.Row increases. Is that what you want?

 

[Michael M]

And it might be simpler to post the code, so we can see how you got to the current position !

 

[Mike54]

Yes, I think that's what I'm trying to do, so regardless of where someone selects on the grid it can always look up the value stored in row 8

 

[Mike54]

Let me see if I can strip it down to something managble, at the moment it rus across various modules etc.

 

[Andrew Poulsom]

You aren't passing anything to the Offset property in your code. You are using the Cells property, relative to the ActiveCell. These two lines are not equivalent:

ActiveCell.Offset.Cells(R - 8, 1)
ActiveCell.Offset(R - 8, 1)

 

[Mike54]

Thanks Andrew, Okay I'm not sure I understand but at least it point me in the right direction i will play about with that and see what happens.

I thought what I was saying was that the active cell was now = to the value of the offset cell & add the text.

What is so confusing is that between row 11 & 55 my code does work well

Thanks for you time I'll try a different method

Mike

 

[Mike54]

Andrew,

ActiveCell.Value = Range("J" & (ActiveCell.Row)).Value

Supplies me with the value of the cell in the current Row in Column J

What would the equivalent be to find the value of the cell in the current Column in Row 8

I have tried a number of permutations and keep getting errors

Regards

Mike

 

[Andrew Poulsom]

Try:

ActiveCell.Value = Cells(8, ActiveCell.Column).Value

 

[Mike54]

Andrew thank you so much, that works perfectly and is a lot neater too;

ActiveCell.Value = Range("8" & (ActiveCell.Column)).Value

This was my attempt and of course it did not work.

But having been stalled for so long it's good to be up and running again, thanks & sorry for the late reply

Regards

Mike