VBA code to keep numbers constant

[mkeys4]

Hi guys,

I know almost nothing about writing VBA code, so I'm hoping you all can help me out.

I two arrays of data, O:O and A:M. What I need is a code that, anytime there is a number in say, O3, the numbers in A3:M3 are held constant for the next five cells.

So if you take a look at the attached workbook, O3 has a number, so A3:A7 should all by .00018, B3:B7 should all be .00056, C3:C7 should all be .00067, ect. Since O8 is blank, all of A8:M8 should be blank. Then the code will find another number in O9, so the same thing should happen to A9:A13, and across to M:M.
O14:O18 is blank, so A:M should also be blank for all those rows. The code would then pick up again at O19.
Things may get tricky at O26 because of the #N/A in C26. I can't have C26:C30 populated with an #N/A, instead I need the code to find the previous available value in that column, in this case C24, and populate C26:C30 with that number.

To give you an idea of what it would look like, all of the column in A:M should match pattern in O:O.

A note on the ranges: they need to be named because I'm constantly adding and deleting columns.

Please ask me any questions if I'm being unclear. I'll get back to you immediately as I'll be watching this post.

[Solus Rankin]

you have an N/A in the first row. Where would you pull the value for that?

[mkeys4]

Hi xero,

Are you referring to B2? Since O2 is blank, A2:M2 should also be blank. The code wouldn't find a number until O3. However, if that's somehow an issue to start the code, you can copy B3 up to B2.

[Solus Rankin]

I think the sheet you're going off might be different than the sample you posted. I don't think the rows are matching up

[mkeys4]

Oh! I'm sorry. The format I had to save it in deleted the first row because it was blank. Let me revise:

So if you take a look at the attached workbook, O2 has a number, so A2:A6 should all by .00018, B2:B6 should all be .00056, C2:C6 should all be .00067, ect. Since O7 is blank, all of A7:M7 should be blank. Then the code will find another number in O8, so the same thing should happen to A8:A13, and across to M:M.
O13:O17 is blank, so A:M should also be blank for all those rows. The code would then pick up again at O18.
Things may get tricky at O25 because of the #N/A in C25. I can't have C25:C29 populated with an #N/A, instead I need the code to find the previous available value in that column, in this case C24, and populate C25:C29 with that number.

In regards to your question, Are you referring to B1? Since O1 is blank, A1:M1 should also be blank. The code wouldn't find a number until O3. However, if that's somehow an issue to start the code, you can copy B2 up to B1.

[Solus Rankin]

Okay, let me modify my question then, lets move to Row 25 column C. If you notice that cell is N/A. We can't take the value above it because it will be blank (no value in C), we also don't want to copy the N/A to the next 4 rows. What would you like to happen with this value?

[mkeys4]

Okay so in that case, you would take the number in C22 (.00065, the most recent available number) and fill that into C25:C29. C23 and C24 are still blank.

[mkeys4]

Oh, and in regards to the named ranges, A:M can be one range, and O should be another

[Solus Rankin]

Try:

Please Login or Register  to view this content.

[mkeys4]

Hi Xero,

Thanks you so much for your code! I see that only A:C is delineated. Will it work for A:L?

[Solus Rankin]

Sure, I thought you only wanted the first three columns done. If you would like to do A:L I will amend the code to loop through.

[Solus Rankin]

This seems like a lot of duplicate date but maybe:

Please Login or Register  to view this content.

[mkeys4]

Hi Xero,

This code is great. Except I'm using it in a sheet that has data on both sides of the ranges, and this code is clearing all rows. I need to confine the code to A:L

[Solus Rankin]

Please Login or Register  to view this content.

[mkeys4]

What I mean is that the "O" reference range and the A:M range are not next to each other. So they need to be distinct, if that makes sense

[Solus Rankin]

Reference range as in named range? they are distinct or discrete.

[mkeys4]

There are columns of data between the range (O:O) that is determining whether or not to clear A:M, and A:M. When I run the code, it's still clearing all rows, so I thought that might be the issue

[Solus Rankin]

I thought you wanted it cleared if there was no data in column O?

[mkeys4]

I do. The code works exactly as I need in the workbook I gave you. But for some reason, even when I add Range("A" & l & ":M" & l).ClearContents it still clears all the data between this range and the "O" range. I thought that might be because, in my sheet, the O range is in fact closer to AA:AA, and there is a lot of data between A:M and AA:AA

[Solus Rankin]

That means you'll have to change other parts of the code to column AA or whatever column you're actually testing.

[mkeys4]

This is what I've changed the code to. In my sheet, A:M is actually O:Z, and O is actually IU. There are two rows of "headers", so the data doesn't start until the third row, so l=3. Can you spot a problem?

Please Login or Register  to view this content.

[Solus Rankin]

The array loops and the adjustments for #N/A aren't going to work. The loop for the arrays loops through columns 1-12 and the search for N/A uses the end(xlup) function to find empty rows which might not be empty if you have additional data not included in your sample.

Can you redact any sensitive information from your actual ws and post it?

[mkeys4]

Sure just give me a sec to make up the workbook

[mkeys4]

Okay here we go. So in this case, A:M is actually O:Z, and O is DB. I've bolded them so you can see

[Solus Rankin]

In this case? Do which columns/rows etc the information is in vary?

If you were to run this another day would the columns change?

[Solus Rankin]

Maybe

Please Login or Register  to view this content.

This won't give you the proper values if the columns change order.

[mkeys4]

The "blocks" wont change. So O:Z will always stay togther. I might add a column in at the end to make it O:AA, but I'm not going to separating the block

[Solus Rankin]

But you will move the data in Column DB which is the column you're referencing. If the ranges will be constantly moving then you'll have to use named ranges or another reference. If you're going to use named ranges then we'll have to change the loops.

The data doesn't have to be static, but if the layout of the document is not static then its much hard to program for the type of formatting changes we have here.

[mkeys4]

Would you mind doing with with named ranges?

[mkeys4]

For some reason the code still doesnt work. I tried copying all the data into a new sheet so that O and A:L are next to each other, and it's still deleting data to the right of O

[Solus Rankin]

The macro will only work if its in the column you posted as an example.

[mkeys4]

Even if I change the ranges?

[Solus Rankin]

The named ranges? They aren't name until the end because the data is dynamic. You never know which rows will be removed.

[mkeys4]

I see. So the ranges need to be named at the beginning of the code and then referenced throughout. Something like
Const range1 As String = "A:L"
Const range2 As String = "O:O"

Or Range("A:L").Name=

Sorry, don't know the syntax that well, but is this possible?

[Solus Rankin]

What you're asking for is possible, I just don't know if it will achieve what you're looking for.

I'm still struggling with the scope and purpose of this project.

[mkeys4]

I'm looking for the code to do exactly as it's doing now, but I need each range to be confined so that it's not deleting across the whole workbook. I don't mind having to go in and change the ranges if I insert rows, but I need to be able to do that. So that I can go in to the code and change Const range1 As String = "A:L" to Const range1 As String = "K:Z" and it will work.

[Solus Rankin]

It wasn't deleting anything but the target rows in the sample workbook you provided me.