VBA to Autofill instead of =formula

[Coretex]

Hello, I have a timetable where I have a reset button that wipes it all. In this wipe I need it to retain the formula that was once in it.

The formula is =IF(definedTime=B2,"Do this", " ") this is then dragged horizontally

B2, C2, D2 etc going horizontal that is the time, like 9:00 , 9:30, 10:00 etc. It is easy to drag a formula but I need it to be automated in the button, I hope this is enough information, thanks

 

[tygrrboi]

Where is your data? What part of it would you like to wipe?

Would something like this work for you.... (test this on a copy of your data so you don't lose anything)

Range("B2:F50").SpecialCells(xlCellTypeConstants).ClearContents

(changing the range to your actual range)

 

[Coretex]

Where is your data? What part of it would you like to wipe?

Would something like this work for you.... (test this on a copy of your data so you don't lose anything)

Range("B2:F50").SpecialCells(xlCellTypeConstants).ClearContents

(changing the range to your actual range)

Hi man, that range is just C2, C3 etc... but I lose the formula currently with the button as it wipes everything, in your VBA where does the formula go?

 

[tygrrboi]

I don't think I understand.

You have some group of cells. Some have data that's manually input. This input triggers the formulas that already exist in some of the cells to make a value, otherwise they are blank.
Is this correct?

What the code I gave earlier does is just delete the cells that do not have formulas in them. The formulas are never deleted but should remain blank because of the If statement....

That is my understanding. If this is not correct I need more specific information about your file as a whole.. not just an example of a few cells or one formula.

It also might be useful to see the code that clicking the button calls. Can you paste that here?

 

[Coretex]

Hi, sorry for the vagueness, basically what I am trying to do is movedata into my timetable from an Agenda tab. I am probably going about this the wrong way however.

What is currently happening, on the other tab there is an Agenda view, with class start and end times. This is populated from my data sheet, it is just a List of times, e.g 9:00, 9:30, 10:00 etc. I am then trying to get this data to be put into the timetable.

The timetable is very basic;

9am 9:30 10 10:30
Mon
Tues
Wed
Thur
Fri


The current formula that I have in the timetable =IF(lessonOneMonday=B3,moduleInfo,"")

lessonOneMonday being the start time of the class, B3 being the time at the top, so 9am. This variable just needs to be the time that is above the cell, so you can see a simple drag of the cell will make this happen, I just can't replicate it with my Clear button.

If you have any idea of how this could be made better that would be great, my other issue that I have yet to face is to have the End time of the class, and have it populate all cells up to that and then merge them haha.

Thanks for any advice

 

[tygrrboi]

Why not leave your table alone and just clear the cells which hold the named ranges like "lessonOneMonday" wherever that may be...
What I mean to say is that your formula will show a blank cell if lessonOneMonday is nothing. What's the point in clearing the formula if you will use it later when you add lessonOneMonday or change it? Just clear the named range contents.

 

[Coretex]

Why not leave your table alone and just clear the cells which hold the named ranges like "lessonOneMonday" wherever that may be...
What I mean to say is that your formula will show a blank cell if lessonOneMonday is nothing. What's the point in clearing the formula if you will use it later when you add lessonOneMonday or change it? Just clear the named range contents.

Hi, the reason for this is that I have some VBA that merge the cells and unmerges them, from start to end time. When the unmerge happens it loses all formula in the other cells.