Code/Macro that takes fluctuating info in one column and makes static in another column.

**Sonny Crockett** · 09-18-2013, 06:12 PM

Hello all,

Attached is a spreadsheet example of my issue.

As you can see from the attachment, I run the report generator for the specific fund number, it populates new data in column C that I have to copy/paste into column B. The SS is just a short example, but the list of funds in column A can reach well into 100+ rows.

Cell B3 can't be B3=C3 (and then dragged down) because when I run the report generator for another fund, it will be blank.

For awhile I had a button with a macro assigned that when after I run the report generator I would click it, and it would take the first row, copy it, and paste it onto another SS. This didn't work too well, because sometimes the first 10 or so rows were the same fund, so I had to run the report generator over and over again for the same fund instead of just copying a block at a time and pasting over to B.

Basically I'm trying to find a way to program it to where it takes all the similar funds in column A, and then takes the generated report in C, and plugs it into B.

Anyhoo, if anyone has done something similar let me know! Thanks!

**xladept** · 09-18-2013, 06:49 PM

Hi Sonny,

Save your workbook as macro-enabled and paste this event-code into the sheet module:

Please Login or Register  to view this content.

**Sonny Crockett** · 09-18-2013, 07:04 PM

Thanks for the quick response. I could definitely use that info for other spreadsheets!

Unfortunately, that code seems to pull in whatever is in C to B, so when I run the generator that pulls info into C for the fund number in A, it will put blanks in B for all the funds not queried in A.

**xladept** · 09-18-2013, 07:14 PM

Do you mean blanks or nulls? If nulls then try this:

Please Login or Register  to view this content.

**Sonny Crockett** · 09-18-2013, 07:51 PM

That works great! Could I also use that for text strings in column C? What if it's a little tricky - for instance if for the non-queried funds in column A, column C returns "No Qualifier Data." But the tricky part being that "No Qualifier Data" could be the correct info that would be returned in column C when running the generator? Like if I entered this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If Target <> "No Qualifier Code" Then Target.Offset(0, -1) = Target
End If: End Sub

And the correct info pulled from the generator in column C was in fact "No Qualifier Code," what could I add to the code that will tell it not to update B?