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

Mike54

Active Member
Joined
Apr 17, 2002
Messages
257
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why are you offsetting ActiveCell by ActiveCell.Row - 8? The offset will increase as ActiveCell.Row increases. Is that what you want?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
And it might be simpler to post the code, so we can see how you got to the current position !
 

Mike54

Active Member
Joined
Apr 17, 2002
Messages
257
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

Active Member
Joined
Apr 17, 2002
Messages
257

ADVERTISEMENT

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

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

Mike54

Active Member
Joined
Apr 17, 2002
Messages
257

ADVERTISEMENT

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

Active Member
Joined
Apr 17, 2002
Messages
257
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
 

Mike54

Active Member
Joined
Apr 17, 2002
Messages
257
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top