Select Column to search instead of hardcoding into Macro. Can't get it to work.

[cjs_007]

Hi All

I have this little snippet of code (which was produced by this wonderful forum!) which is part of a longer macro that is working fine.

For i = 1 To last
0 If Left(Trim(.Cells(i, "B").Value), 18) Like "####[-]###[-]####[-]####" Then
DestRow = DestRow + 1
ws2.Cells(DestRow, "A").Value = Replace(Left(.Cells(i, "B").Text, 20), " ", "")
ws2.Cells(DestRow, "J").Value = Replace(.Cells(i, "B").Text, ", ", "", 22)
ws2.Cells(DestRow, "B").Value = Replace(.Cells(i, "I").Value, ",", "")


My issue is that it is searching column B for a value and then pulling the value Column I. depending on the worksheet I sometimes need it to search/pull different columns other than B and I.

I basically want it to ask me "what column is the description" and then allow me to pick a column and that would update the bold "B", and then "what column is the value?" and then allow me to select a column and that would update the bold "I"

Is this possible?

[rcdavis28]

Maybe try this:

Please Login or Register  to view this content.

In the replace part of your code instead of , "B". Put .Cells(i,wkrng).

[Winon]

Hello cjs_007,

Maybe this could help you getting started?

Please Login or Register  to view this content.

Regards

[cjs_007]

Maybe try this:

Please Login or Register  to view this content.

In the replace part of your code instead of , "B". Put .Cells(i,wkrng).

This is what I had found and was doing but I am getting Type Mismatch error so wasn't sure what I was doing wrong.

I should have been more clear - the macro runs through each sheet and searches column B of each sheet so I'm not sure if that is an issue or not.

[rcdavis28]

It's hard for me to tell without seeing a sample workbook

[cjs_007]

I appreciate the help.

I have attached a sample worksheet with macro included.

Budget_Maker1 is the working macro with the end result I am looking for. I have it set to pull the values in Column C for "Year-to-Date" Actuals. However sometimes I will want to run the exact same macro but pull Column D or E instead - and in other sheets the GL accounts are not in column B but instead in Column A.

Budget_Maker is with your portion added.

I want it to do exactly what you're trying to get it do - I just can't get it to run!

I know it is pretty messy but I'm not worried about that at all! I was just hoping I can add some functionality that makes it easier for others to use this as well without others having to go in and update the Macro each time.