Conditional Macro - Insert Text And Lock All Duplicate Cells Except For First One

Sep 16, 2012

I am new to VBA but I am trying to insert text and lock all duplicate cells except for the first one. Below is the macro I am trying to make. I keep getting an error on the line:

If(COUNTIF($A$9:$A$76,A9)=1,FALSE,NOT(COUNTIF($A$9:A9,A9)=1))

This is my macro:

Sub SetVoters()
'
' SetVoters Macro
'

'
Sub SetVoters()
'
' SetVoters Macro
'

'
ActiveSheet.Unprotect

[Code] ......

View 1 Replies


ADVERTISEMENT

Creating Macro To Insert Text Into Predefined Cells

Apr 22, 2014

I had posted earlier to see if there was a way to protect certain cells from being updated when using a linked excel spreadsheet to access. Since there have been no replies I'm assuming not.

Different approach, can I create a Macro that would run after the update that would insert my required text into each cell from A2 through to R2.

The text would always be the same and always in exactly the same cells. This would need to be an insert into and not a replacement of the data already in those cells.

A1 B1 C1 D1
State
BUName
InsuranceCompanyName
Claim
ROnumber
Renter Last Name

[Code] .......

A2 B2 C2 D2
CategoryFilter CategoryFilter CategoryFilter NoFilter

The text under each of the A2, B2, C2 etc is what I would want the macro to do for me, essentially putting back what the update from the linked source removed.

View 3 Replies View Related

Macro To Insert A Line And Duplicate Data

Oct 2, 2006

Worksheet excel with multiple lines and colums. I need a macro to automatically insert a new Line where the cursor is located, and duplicate data from previous cells: let say cursor is located L10, macro will insert L11, and duplicate L10 -C1 data to L11 -C1. I have a macro that does this function, but the Line insertion is always at the same line.May be the macro should "read" first the cursor position, Lx and then move down +1 line prior to duplicate the data.

View 2 Replies View Related

How To Make Macro To Lock Certain Cells Connected To Another Cells Value Then Copy Daily

Mar 29, 2014

am making Excel for private use, i need the following makro to be active, here is the idea because i didnt yet work with makros at all:

If A1 = False
then Range B1:D1 will be locked cells

If A1 = True
then Range B1:D1 will be unlocked cells

Those rules apply to 1 day in the year.

the range A1:D1 will be copied about 400 so the makro should be active to each day separatly.

Check the picture attached for example of one day

View 1 Replies View Related

Insert Text With Conditional Formating

Jan 9, 2008

I have a list of numbers say in column A, but when the number "41", "25", "90", or "92" is in that column, I want a text message to print 6 columns to the right of it (same row) that says "service".

View 9 Replies View Related

Insert Text And A Cell Value In Conditional Formatting?

Aug 8, 2014

Is it possible to insert text an a cell value in conditional formating, i,e Ive got the conditional formatting:

=AND(H$4>=$B5;H$4<=$C5)

I want to insert this text whenever this condition is true once and not to repeay it:

="Load " &TEXT(G$5;"dd-mmmm") -- where G$5 is a vallue cell_

View 1 Replies View Related

Dynamic Macro To Lock Cells That Contain Any Value

Feb 10, 2014

I'm currently using this macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng
Dim MyCell
Set Rng = Range("A1:AA7672")
For Each MyCell In Rng

[Code] ....

The problem is that it is not dynamic and it's super slow. The larger my range is the slower it operates and right now it's almost unusable.

The workbook I am using gets touched by 10 different people and I want to prevent tampering. I want for the workbook to be, when protected, have all the blank cells editable and once a value is inserted, it cannot be edited again and not work so unbelievably slow.

View 5 Replies View Related

Automate Macro And Lock Cells

Oct 6, 2008

I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)

At the moment however, you must run the macro manually to export the data.

Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!
Any pearls of wisdom folks?

I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!

View 14 Replies View Related

Macro Filter And Then Lock Cells

Oct 18, 2012

I have a search form that will filter the result based on the requested category. Is it possible to lock the cells once it has been filtered and then remove the lock after they close the workbook?

View 2 Replies View Related

Macro To Lock Cells After Data Entered

Mar 22, 2007

I need a macro which will lock the cells after data is entered in the cell once. Cell should become non editable and should be only edited after a password is provided.

View 4 Replies View Related

Mark Duplicate Values :: Insert Word Duplicate Next To Row

Jun 12, 2008

I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:

12345 a
12345 a DUPLICATE
11111 b
23123 b

Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?

Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub

View 3 Replies View Related

Macro To Lock All Non-Blank Cells After Saving Workbook

Jul 23, 2014

I have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.

When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully".

View 4 Replies View Related

Macro To Lock A Number Cells After Changing A Cell

Jan 5, 2009

I have an excel sheet which is protected (mainly so that the users will not change any formulas) the excel sheet is basically a list

Just to put you in the picture
Column B Is PO Number
Column C Is Remarks
Column D Date
Column E Description
Column F Supplier
Column G Amount

I would like that when Column C changes to "OK Checked" column B C D E F G are protected and locked. But obviously for that particular row.

For example the input of Cell C15 changes to "OK Checked" then B15, C15,E15,F15,G15.
the macro must NOT be fixed to a particular row (in this case 15)

View 9 Replies View Related

Lock Cells Automatically After Entering Via Macro Code

Apr 23, 2008

I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.

View 2 Replies View Related

How To Insert Conditional Language In A Macro

Jul 31, 2008

I would like to run a macro against a worksheet that will have variations in the amount of data (rows not columns). The column headings will always stay the same.

Once the basics in the macro run (formatting) I will need a certain statement to "lookup" values in a column (see values below) "count" them and give me a total.

I have attached a before and after file showing what I would like the macro to ultimateley do because it is so time consuming. Any assistance would be most appreciated.

.083=0-2hrs
.125-.50=3hrs-12hrs
.541-1.0=13hrs-1day
1.04-5=25hrs-5days

See SLA Response Days (column)

View 9 Replies View Related

Insert Worksheet Macro Conditional

Mar 9, 2007

Still struggling with the calculator. Is there anyway to get a macro to insert a set number of worksheets. The set number would be the sum of two fields on Sheet 1.
Example attached. I have searched the forum and can now use a macro to insert a worksheet but have no idea how to tailor it.

View 2 Replies View Related

Macro To Lock Cells In Protected Sheet Depending On Criteria

Dec 13, 2007

I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.

What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?

Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.

What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.

What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.

Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?

PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.

View 14 Replies View Related

Lock Cells To Prevent Predefined Range Macro Copying

Oct 30, 2012

I have a spreadsheet that allows users to paste set data from a PDF Image (using OCR) straight into Excel and then use the MID function to split the data accordingly.

Unfortunately, the OCR isn’t too intuitive and gets it wrong sometimes.

So to counter this, in another sheet (in the same workbook) I have a manual input section, and a simple macro button that pastes this data into the same fields where the OCR text would be, so that the main sheet works exactly the same way as before.

The problem is, and most likely due to the simplicity of the sheet, if a combination of OCR pasting and manual inputting is used, when I hit the paste button, it over rides the OCR data with blank cells

In the link below I have shown what is currently happening (1, 2, 3), and an example of what I would actually like it to do (4, 5, 6).

Example - Online Spreadsheets - EditGrid

So, in the 2nd scenario, I would like “5” to recognise that the respective cells in “4” already contain data and fill them ‘Grey’. This I have already achieved with basic conditional formatting.

However, I need to take it 1 step further and say that if the parent sheets cell (Auto OCR) contains data, as well as filling cells (in sheet Manual) lock these cells off and prevent the end user from adding data and/ or being copied over to the parent sheet.

Is this possible?

The result then being the parent sheet with both OCR text and copied text from the manual input sheet.

Both sheets are protected anyway and only allow for user input in certain areas, so is it even possible to apply further protection once the sheet is locked already?

View 1 Replies View Related

Insert Row Macro To Copy Down Conditional Format & Functions

Jul 6, 2009

I have a spreadsheet whereby in each cell of a row there may be a formula or/& conditional statement applicable. I would like to create a macro that can perform the insert of the new row as well copy down from cell above applicable functions as well as conditional formats that maybe resident. Can this be done?

View 8 Replies View Related

Lock Cell Contain Formula But Allow To Insert Row?

Nov 8, 2013

i am having problem with doing team, i would like to lock cell contain formulas but allow users insert row.

View 2 Replies View Related

Remove Duplicate Text From A String In A Row Of Cells

Sep 29, 2009

I have a large worksheet and I managed to duplicate data in a row of cells.

MOT 1/F1,
B/P 1f2,
B/P 1f2


In the string above I need to delete all text from the first comma to the end of the string so the above would look like:

MOT 1/F1

This is an example in one cell of many. The text prior to the first comma may or may not repeat and it is the only portion of the text I wish to keep. The first comma is not always in the same place (character count position).

View 2 Replies View Related

Duplicate Serial Numbers With Multiple Cells Of Text?

Feb 27, 2014

my spreadsheet has duplicate serial numbers in column A and the corresponding row in column B has mutliple descriptions for the same serial number. I need to combine those descriptions into 1 cell rather than having multiple rows for the same serial number. is there an IF formula that I can combine with a concatenate that will capture what I need?

View 1 Replies View Related

Conditional Cell Lock

May 29, 2008

I have an sheet(see attached) with some dummy data, now i want to lock (with password) only those cells depending on the dropdown (highlighted in sheet).For eg,if we select A from dropdown of col c and then locked from dropdown of col D , it should lock the complete row(s) of value A in col H and rows with value B shall remain editable.

View 14 Replies View Related

Duplicate A Macro To Different Cells

Jan 15, 2009

I have a worksheet change event that prevents a person from entering a positive number in 2 different cells. If they enter a number in one cell, the other locks, and vice versa. The code is below.

View 4 Replies View Related

Macro To Insert Text

May 31, 2007

I need a macro that will insert text into merged cells. I have merged blocks where all will be exactly the same size one after another. (there are 19 blocks). The problem is that each month the size of the blocks change.(see sheet for example). Right now blocks are 8 rows will get to 16 by end of year. In each block I need a "- (city) (Name)" Each block has a different city and name that goes with it.

It would be an awesome time saver If I could click a button and have the city and names be entered into the appropriate spot no matter what month were in.

View 6 Replies View Related

Conditional Formatting Formula Lock?

Apr 29, 2014

I have a sheet which contains conditional formatting formulas.How i can Lock or protect the forumluas to view others.

View 2 Replies View Related

Conditional Cell Lock Unlock

Dec 10, 2006

None of the information Ive found on the forums seem to work in my sheet and Im not sure why. After protecting my sheet/workbook, I would like a particular input of a cell to Unlock another cell.

ie if the contents of cell A1 is the phrase "TRUE", then cell B1 will be Unlocked for editing. If the contents of A1 is "FALSE", then B1 shall remain locked. From what i can tell, this must be carried out using VBA code. If anyone can resolve this issue, Id be also very grateful if you could explain what each line of the code means

View 6 Replies View Related

Insert Text Into Cells After New Tab Created

Nov 25, 2008

I have a button that will create tabs based off of information from a text box and now I want to select one sheet and insert text into certain cells so when they create a new tab information that is generally going to be there is autogenerated. I have an example of my code below. Please point me in the right direction cause I cant find anything really helpful that I havent tried. Everything is under the "gateway" then "New tabs" and PBG-Activity list is the only one with this macro.

View 2 Replies View Related

Insert Text In Cells Based On Another

Mar 12, 2008

I have an excel sheet that looks like this:

IMAGE 1

(The sheet holds more data but not needed for this)

I have two buttons; BUY and SELL

I need a vba-code that inserts either BUY or SELL in row 6 (buy/sell) and insert a text (code) in row 7 depending in this information. When I push either Buy or Sell

IMAGE 2

So.. When I push the button SELL (already made) the action Switch should get Sell and actioncode SO, and Redem1 and Redem2 should get Sell and actioncode RA and RN

So.. When I push the button BUY (already made) the action Switch should get Buy and actioncode SI, and Subsc should get Buy and actioncode SA

(When the button is pushed I have a autofilter that hides either all the sell or all the buy)

IMPORTANT: There is NO range!! the list goes on, and changes so I need it to work on x amount of rows. !

View 3 Replies View Related

Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells

Jul 18, 2006

Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved