VBA to insert formulas only in selected cells

**htlf** · 07-29-2021, 12:24 AM

Hi all,

I was just seeking some advice on how to write a macro that will insert a formula only if a condition is met.

The goal here is I have a column of dates and want to run an xlookup formula to pull in data only if the date is within the past 30 days.

I want to macro to look for example at the date in A1, and if that date is less than 30 days before todays date, then run an xlookup and write the value to B1.
I am hoping to set this up to look through up to 1000cells (A1 to A1001). Below is the code I attempted to write but does not work seem to work at all as I am quite new to this.

-----

Please Login or Register  to view this content.

----

Any suggestions on how to do this would be greatly appreciated.

**DavidBowman** · 07-29-2021, 09:57 AM

I'm not sure why you are writing a macro to accomplish this goal. This seems like you could just use the formula in the spreadsheet to accomplish the same goal. Having said that, in your macro the formula closes out the xlookup but does not close out the if function. It should look more like:

Please Login or Register  to view this content.

**htlf** · 08-02-2021, 11:21 PM

The reasoning for the macro is if the range is not within the past 30 days then I want nothing to change in the existing cell.
The xlookup is looking at another table that only contains only the last 30 days of data. If I just had the formula in each cell then the when the data source updates to no longer include old data then the xlookup would break.

Thanks for the pickup on the way the macro is written, I will try that out.

VBA to insert formulas only in selected cells

LinkBack

Thread Tools

Rate This Thread

Display

VBA to insert formulas only in selected cells

Re: VBA to insert formulas only in selected cells

Re: VBA to insert formulas only in selected cells

Thread Information

Users Browsing this Thread

Similar Threads

Is there a Macro that can insert multiple photos to a selected range of cells.

Insert cells, fill formulas down, set formulas for dynamic range

Code to insert rows between selected cells

Copying formulas down cells:proper cells are selected?

Macro to insert formulas to certain cells respective of cell selected

Insert text next to selected cells

Insert a line in chart and enter selected cells sum

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions