VBA to Add Column to Table Based on Value in Cell

**Sammydasalmon** · 04-08-2018, 07:06 PM

Hi all,

I am working on creating a table for calculating daily rate and margin for a company. We are currently working with several rates. However, in the near future there is a chance for adding one on two more. Attached is part of the example for the first page. Now I cannot figure out which formula to put to automatically populate the field "column1" when we enter new rate, not listed already. Can it be done without using VBA? I was considering conditional IF function with match for all the previous cells.
Any ideas will be appreciated.

Thank you in advance

**Sasha 99** · 04-08-2018, 07:54 PM

How do you want to calculate daily rate and margin ?

**Sammydasalmon** · 04-08-2018, 07:58 PM

I already have all the formulas for them set up I am lacking only this bit. I just the number entered under daily rate on the left to be shown in the last column ("column1"). I am not sure i explained that one clearly before.

**Sasha 99** · 04-08-2018, 08:15 PM

You can just write = then choose your cell you want to show its content
But I am not sure too that I understand your request

**Sammydasalmon** · 04-08-2018, 08:23 PM

I wanted a check for repeating values.

**FDibbins** · 04-09-2018, 01:55 AM

Sorry, but Im having a hard time understanding what you want to do here

What is "column 1" supposed to be doing and how does it fit in what what you have there? You said you had some formulas there, I cant see any?
Can you some some examples of what you expect??

**AliGW** · 04-09-2018, 02:24 AM

So, each time you change the rate in D3, you want a new column to be added to the table with that value as its header like this:

Excel 2016 (Windows) 32 bit

	I	J	K	L	M	N
2	Day rate	£550	£560	£570	£575	£900
3	Revenue	£399,300	£406,560	£413,820	£417,450
4	Margin	18.40%	19.86%	21.27%	21.95%

Sheet: Sheet1

Is that it?

**Sammydasalmon** · 04-09-2018, 02:59 AM

HI Ali,

Sorry for the late reply. Yes that is exactly what I want. If possible with check for matching with previous numbers.

**AliGW** · 04-09-2018, 03:01 AM

OK - well, you will need VBA for this. I cannot help you myself, but can move the thread to the correct forum section for you, which I shall do now.

I've also updated your thread title: in future, please make your thread titles much more specific and explicit.

**xlnitwit** · 04-09-2018, 03:09 AM

Perhaps this in the worksheet's code module

Please Login or Register  to view this content.

**AliGW** · 04-09-2018, 03:12 AM

Does it need the line about adding the £ sign if the cell is already formatted to show it?

**xlnitwit** · 04-09-2018, 03:19 AM

As written, yes- the Value property will ignore formatting. We could simply use the Text property though, if the format of D3 matches the way the column headers should look.

**AliGW** · 04-09-2018, 03:24 AM

Interesting - thanks.

**Sammydasalmon** · 04-09-2018, 03:41 AM

@Ali Thank you very much, I will keep in mind your tip regarding the heading.

**AliGW** · 04-09-2018, 03:46 AM

Has the code provided in post #10 worked for you?

**Sammydasalmon** · 04-09-2018, 03:59 AM

Hi xlnitwit,
as I am quite new to VBA. Can you please tell me what should I do after adding the code at the worksheet code module.

Thank you in advance for your cooperation.

**YasserKhalil** · 04-09-2018, 04:01 AM

Hello
You should save the workbook to the extension xlsm or xlsb which supports the macros

**xlnitwit** · 04-09-2018, 04:45 AM

As YasserKhalil said, you'll need to save the workbook in a macro-enabled format, but other than that, just change the value in D3 and you should see the table add a new column as appropriate.

**Sammydasalmon** · 04-09-2018, 04:46 AM

I inserted the code into the workbook and now are trying to start it, but still facing issues. I believe the code is fine the problem is in my skills. I will check some video to get a better idea of how to make it running.

**Sammydasalmon** · 04-09-2018, 04:49 AM

Ladies and gents thank you all for the help. Issue SOLVED, the code provided is working perfect.

Thank you all for the fast responses.

**AliGW** · 04-09-2018, 05:17 AM

Good to know.

VBA to Add Column to Table Based on Value in Cell

LinkBack

Thread Tools

Rate This Thread

Display

VBA to Add Column to Table Based on Value in Cell

Re: If function needed

Re: If function needed

Re: If function needed

Re: If function needed

Re: If function needed

Re: If function needed

Re: If function needed

Re: If function needed

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Re: VBA to Add Column to Table Based on Value in Cell

Thread Information

Users Browsing this Thread

Similar Threads

Select cell from table based on values in row 1, and column A

find a cell in a table based off column and row criteria

Selecting highest date value from table column based on the value of a cell in the table

[SOLVED] Fill a cell in Column B based on the text in Column A using a table on sheet 2

vba code to filter pivot table column labels based on cell vaule

Lookup Column number in a table based on a cell value

Writing to cell in table based on column and row names

Bookmarks

Bookmarks

Posting Permissions