Avatar of Jagwarman
 asked on

vba to add items to an existing list

I have a [very] hidden list which is usedin a Vlookup, if the Vlookup cannot find what it is looking up it returns 'Not inRegion List'

I would like to add a button to the worksheet that allows the user to add the details to the bottom of the list without the need of unhiding the sheet.

The list is in a worksheet called Static and has two columns. Column A is Market column B is Region. The list has been named as Regions.

Can an expert assist with this one please.

Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon
Joe Howard

Sheets("Static").Range("A" & Sheets("Static").Range("A" & Rows.Count).End(xlUp).Row).Value = "whatever value"
Sheets("Static").Range("B" & Sheets("Static").Range("B" & Rows.Count).End(xlUp).Row).Value = "whatever value"


pls try

With Sheets("Static")
    .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row+1).Value = "whatever value"
    .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row+1).Value = "whatever value"
End With 

Open in new window


We have to add 1 not to overwrite data


Hi Macroshadow.

thanks but I probably did not make my spec clear enough. What I need is a pop up window that willallow the user to in put the information that will then add it to the bottom of the list.

I have tested yours and it only seems to add it to the bottom of the existing kist so if I run yours once it adds whatever value then if I try to add [say Friday] it overwrites whatever value.

So in a nutshell I am looking for the code to present the user with a pop up that they can enter the details in which will then add the details to the bottom of the list.

many thanks
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Joe Howard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Exactly what I need thanks