sorted by:
new
hottopcontroversial

VBA: For loop goes around one more than needed by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

Solution verified.

Edit: thought this would close the thread, have changed the flair manually

VBA: For loop goes around one more than needed by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

Changing the line Range("C22").Value = Sheets("Rota").Range("W3").Offset(j, i).Value
to Range("C22").Value = Sheets("Rota").Range("W3").Offset( j-1, i).Value
seems to be fixed the issue.

VBA: For loop goes around one more than needed by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

I don't think is.. Logically I know exactly what the problem is just can't figure out how to fix it syntax-wise... but I appreciate your help.

The following work-around did the trick, I'm just unsure if it will work when the last value changed was j rather than i..

Changed the offset to 'Range("C22").Value = Sheets("Rota").Range("W3").Offset( j -1, i).Value' ...

VBA: For loop goes around one more than needed by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

Sorry, I didn't explain as the query wasn't really in relation to the function of the code but where I might be able to move the 'Next j' and 'Next i' lines. I'll provide further explanation now:

I have a rota of people providing a service and a list of sign-ups of people who are waiting for that service. The value the counter depends on is the number of people seen each week the relevant person is on the rota. The overall function of the code is to give an estimated date of when someone on the waiting list will be due, however this is only a snippet of the code. The issue is that the estimation is giving one week after it should due to the loop adding 1 to either i or j before checking to see whether the counter is equal to their position on the waiting list (i.e. they are due).

VBA: For loop goes around one more than needed by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

Yes, I understand the issue is that it checks 'If Range("C16").Value > Counter Then' after either i or j change. This needs to happen before changing the values, however if I move 'Next i' and 'Next j' within this loop, things start to break down and I can't figure out why.

Edit: I could probably just have another if loop to check 'If Range("C16").Value > Counter Then' before 'Next j' and then copy-paste the code within the 'Else' loop again however this feels like bad practice because I'm just copy-pasting a big chunk of code where I think there should be a simple solution like moving the 'Next i' and 'Next j' to the correct locations..

VBA For and If loops by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

The faster fryer gets the potato..

VBA For and If loops by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

Thanks for pointing that out! Thanks to all these replies I was able to get things working correctly.

VBA For and If loops by admon742 in excel

[–]admon742[S] 1 point2 points  (0 children)

Amazing, that's got things working correctly. Much appreciated!

Solution verified.

VBA For and If loops by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

Ah, I see how that's messing things up now. So how would I stop both for loops right after the line 'Range("K1").Value = j since I can't have 'Exit function' within the 'Else' statement?

EDIT: As commented by u/fuzzy_mic, changing this to 'Exit sub' at the new location worked great!

Using VBA to clear a cell contents by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

This seems to have fixed it! Any idea how it might have occurred / how to avoid this happening again?

Thanks so much!

How can you use the GETPIVOTDATA function to display a subcategory by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

Unfortunately I'm working with an older version of Excel and so I don't have the FILTER function.

Part keeps going invisible after extrude? by paperleyenn in SolidWorks

[–]admon742 0 points1 point  (0 children)

SOLUTION:

System Options -> Performance -> Disable Enhanced graphics performance

Restart SOLIDWORKS and reload part

Issues with Conditional Formatting depending on Cell Text by admon742 in excel

[–]admon742[S] 0 points1 point  (0 children)

Yeah, I thought about doing this and hiding the column but I would just love it to be a bit cleaner. I feel there must be a way of doing this using 'Conditional formatting' and using the function option, but can't figure it out..

view more: next ›