I need to write a pattern for a postcode. I have the below (very basic I know and loses lots of combinations but i'll deal with that later) but no matter what I enter in the box I get mt error message saying the input in incorrect....
View 9 RepliesIf the user clicks on a cell within a specified range ("DataEntryWindow") which contains formulas, I want the formula RESULT to be copied on to the formula bar for that cell (not in to a cell) - the user can then press enter to replace the formula with the result, or escape to cancel
- you can do this manually by copying the cell as values to another cell, then copying that value to the clipboard letter by letter, then clicking on the original, clicking F2, selecting all the formula text, then pasting the clipboard!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("DataEntryWindow")) Is Nothing Then
curval = Target.Value
Application.SendKeys "{F2}"
'Application.SendKeys "???" ' some keyboard combination to go left, left, left
Application.SendKeys curval
End If
End Sub
Needless to say, it doesn't work. sendkeys seems to do nothing at all. is there an object in worksheet for the formula bar that i can paste to?
I try to use data validation to allow only words using letters b to y. Using a regular expression it would be an easy check for ^[b-y]+$, but obviously this is not supported. I don't want to use VBA. Any ideas?
I thought of replacing b to y by empty string and see if rest is longer 0, but also here I would have to replace flexibly.
I believe it is not possible. Is it? (as long as you don't use additional cells for side calculations)
I have a list of addresses, which are comma separated, all ending with a PostCode
How Can i strip out the PostCode field?
Pre-requisite: I would consider myself to be very poor with excel, based on what I've read on this forum and found on my web-searches. I have a worksheet that has a list of data on the left going vertically, then a summary of this data going horizontally across the top. It is not arranged in such a way that transposing the data will do what I want. I am pulling the 5th word out of the title of each block of the vertical data and need to show this word on the horizontal section.
When I use this formula to pull the 5th word: =MID(MID(MID(SUBSTITUTE(A2," ","^",4),1,256), FIND("^",SUBSTITUTE(A2," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A2," ","^",4),1,256),FIND("^",SUBSTITUTE(A2," ","^",4)),256))-2)
I need to increase A2 to A30, then A58 (up by 28 every time) in every instance in that formula. The fill handle increases the values by 1, instead of 28 (even if I do 3 or 4 instances manually) How do I do this? I've run into this problem in other scenarios, and there HAS to be a way to get around it.
I have a macro that does exactly what I need it to do, but it takes a long time.
There are 4 horizontal sections. The top section (code not included) is the total of 3 sections which are segregated by location.
For the 3 sections:
rows 101 to 172 (section 1), 198 to 269 (section 2), & 295 to 366 (section 3) have the same formula for every other column starting with column F ending with column AT (sums hrs)
rows 173 to 194 (section 1), 270 to 269 (section 2), 367 to 388 (section 3) have the same formula for every other column starting with column G ending with column AU (sums dollar amount)
the columns are corresponding dates
Below is the code snippet that produces the results for section 1. I repeat the same code pattern for each section.
Dim r As Long
For r = 101 To 172
With LaborDetail.Cells(r, "F")
.FormulaR1C1 = "=SUMIFS('Data '!C17,'Data '!C38,RC1,'Data '!C37,R1C)"
.Value = .Value
[Code] .........
In column A I have 10,000 road names with many duplicates. In column B I have the duplicate roads removed with only one left (about 1400). In column C I have a map code for that road, married to column B. Question; how do I get the map code to post to all the equaled roads in column A?
View 4 Replies View RelatedI have produced a basic search/lookup facility on an Excel workbook that simply allows the user to enter a post code and some details will be returned using a vlookup. The document is going to be rolled out to a number of operational users so I want to basically 'lock down' everything I can in the document (basically everything except the data entry cell) and make it fool proof- I have locked all cells apart from the data entry cell and have made the file read only.
The only issue I am encountering is when the cursor is in the one 'unlocked' cell (i.e. the one the users will enter the post code into); it appears that you can break the document. For example, when the cursor is in the 'unlocked' cell, I am able to go to Tools > Options and change various settings including cosmetic colour changes but also cell calculation which breaks the lookup functionality. This is probably enabled as the cell is unlocked, but if I lock the cell, when I protect the document, it disables data entry!
if there is another way of providing this one cell for users to input data into for the vlookup to work whilst locking down the rest of the document to ensure that no-one can break it?
In my file i can insert text to a certain cell and column but this is for one entry only. I am wishing for a run that for every entry, there is a new row.
Here's my file sample project.xlsm
You can see there the user form for a post entry. The user should fill-in code and description and if he hit save, the code and the description should be recorded on list provided the sheet.
I'm not that clued up on VBA or macros but I do have a few i regularly use, I’ve found a thread related to my problem but cant make much sense of it.
Basically i want to post the 3 cell values (C5,C7,C9) into row B20,C20 and D20 respectively. However I want these values to form a list staring with row 20 and working down onto the next available/empty row.
Marking My Post As? How do i mark my post as solved?
View 5 Replies View RelatedI am trying to enter this formula in cell B2 and i just wanted to know if there was a better and quicker way to do this. I need to do this all the way to BP however my list can extend however the pattern will stay the same.
Ws.range("B2").value=
F & LROW * D1 +
K & LROW * D6 +
P & LROW * D11 +
U & LROW * D16 +
Z & LROW * D21 +
AE & LROW * D26 +
[code]....
I need to have a string comparison done in a macro,
I have a files with names similar to "TEVT_GURUPRASAD_WEEK08" and a array of strings having names "Guruprasad,AnilKumar,....etc." I Need to match the name in the array and the name in the filename.
Not sure how too do this, here is a simple example of what I want too do. Just using different buttons to post a number.
View 6 Replies View RelatedI have a list of UK Post Codes that I wish to filter but because of the format I'm getting problems.
The reason is that I'm only interested in the first part of the post code.
Example: Using IP7 6NH and IP32 8LR. If I tried to filter those using greater than then Excel would treat IP7 6NH as larger then IP32 8LR because 7 is larger then 3.
The post codes are all imported from another piece of software and are always in the format of 3 or 4 chracters, then a space and then 3 more chracters. I wish to create another column which only has the first part in it. So I wish to crop IP7 6NH down to just IP7 and IP32 8LR down to just IP32.
Then I want to make a further column which looks at the value in the first new column and returns "In" or "Out" based on some conditions.
These are the conditions. If the cell begins with CO or NR or CB or CM or PE, regardless of the rest of the value, then "Out" should be returned. If the value is between IP1 and IP6 inclusive then "Yes" should be returned.
If the value equals IP7 then "No" should be returned
If the value is between IP8 and IP17 inclusive then "Yes should be returned.
If the value is between IP18 and IP29 then "No" should be returned.
If the value equals IP30 then "Yes" should be returned.
If the value is bewteen IP31 and IP33 inclusive then "No should be returned"
I'm think that it's quite complex and maybe the way to go would be to have two other columns, one containing the "In" values and the other containing the "Out" values, that a formula can refer to when making it's yes or no choice but I don't know how to do that.
I may have missed it, but I can not seem to find where I can customize Excel to add a "paste formula" button. (I have a fair amount of copying to do where I do not want the formats being changed at the same time.)
View 9 Replies View RelatedI have the following code. It takes data from one sheet and inputs into another. When I enter a Customer ID in cell G12, starting on Row 18 and proceeding down, jobs are listed. When the macro posts into the history sheet, it posts the jobs in rows. I wish for each time I run the macro, the info is all in the same row whether theres two jobs or 20 for that customer ID. Right now it is posting like this (two jobs listed):
CustID(G12) | Name(B12) | Address(B13) | Job1(A18) | Job1Date(D18) | Job1Price(G18) |
CustID(G12) | Name(B12) | Address(B13) | Job2(A19) | Job2Date(D19) | Job2Price(G19) |
Im trying to make it look like this:
CustID(G12) | Name(B12) | Address(B13) | Job1(A18) | Job1Date(D18) | Job1Price(G18) | Job2(A19) | Job2Date(D19) | Job2Price(G19) |
Sub InvoiceUpdateHistory()..................
Is there a way with a formula to have a result of one cell pasted in a different cell? ie. C1's formula =if(and(isnumber(a1),isnumber(b1)),b1-a1,if(and(isnumber(a1),isblank(b1)), b1 = d1,"")
A B C D
12/18/06 formula above =today()
if b1 isbalnk it will copy the value of d1 to b1. I dont know if this can be done i have looked at offset and a few others but it doesnt look like this can be done but want to see if i have over looked domething
The spreadsheet I'm working on is kinda hard to describe, but I'll try.
It's a finance calculator of sorts that I need done, and it shows me the total cost of various products. Each of these products require different components, and numbers of components, and also these components all cost different prices. So what I have is the following spreadsheet setup:
I'm trying to extract specific character from a set of text strings which have irregular patterns. For example:
CJWHCon.D005 - JN Holding Inc Corporate
CUSViWind.D404 - Windows Ringtown Vinyl
My goal is to get only the 'DXXX' which sometimes might be only 'DXX'. I've tried using the following formula but I seem to be going wrong somewhere as I only get 'D'.
=MID(F10,FIND(".",F10)+1,FIND(" ",F10,FIND(" ",F10)+1)-1-FIND(" ",F10))
I'm trying to repeat a pattern which outputs 4 equal numbers and then increments that number by 1 for another four row as follows
1
1
1
1
2
2
2
2
and so on
how to do this?
Need a macro that will autofill in an email pattern from a customer list i recieve. So far i have been having to manually do it.. but when have documents over 3k entries.. ouch.
This is what i got.. hope the way i type it in makes sense
A ..........................B ..........................C
First name........Last Name ............Email Pattern
John ..................Smith ...............First name initial, last name@abc.com
John ..................Smith ...............First name initial, dot, last name@abc.com
John ..................Smith ...............First name, dot, last name@abc.com
John ..................Smith ...............First name, initial of last name@abc.com
Is there a macro i can make that will automatically fill in the email based on the criterea found in column C before the @ symbol? then take the required info from Column a and b?
I am a beginner to VBA and Macros, and I have a fairly complicated macro that I am pressed to make.
I am working with alphanumeric data that is unorganized. Here is an example of what it looks like: ...
I am trying to come up with a formula that will distribute the numbers as shown in this diagram:
To get the value of each number divide 42,000 by the last number in the pattern. For example the fifth column from the left would be 42,000/18. That means that each number would have a value of 2,333.33. By the time you reach the half way mark, nine in that example, you should have around 21,000, and the other 21,000 should be distributed between the last half (block 10 thru 18). The formulas would have to work on all of the patterns. Can anyone think of a way to match the pattern shown? It doesn't have to be exactly the same pattern just as long as the number of blocks remains the same for each half, and they total 21,000 for each half without going over 42,000 all together. There has to be 11 rows from top to bottom.
Is it possible to change the borders/pattern of a single cell so that it looks like the image in the attached document? This may seem a strange thing to want to do, but there is a good reason! Merged cells will not suit my purpose. The pattern palette allows a cell to be filled with horizontal lines, but I need there to be just two lines (i.e. dividing a single cell into three boxes).
View 2 Replies View RelatedI made a code that automatically checks the date and then compares it to the date of the last save. If it is a new date then the colors of the tabs will randomize. It can also be done manually via a button. The problem is that I have now realized that the colors are always have the same pattern. Every day the first color is an orange color, no matter how many times I randomized yesterday...and so on. Is there a way to make the randomness not have a repeating pattern?
Sub MMVII_4_9_b()
Dim NewDate
If Sheets("Scrappaper"). Range("F2") = Date Then
NewDate = 0
Else
NewDate = 1
End If
'Determines whether it is a new day or not
Sheets("Scrappaper").Range("F2") = Date
'Updates the date
If NewDate = 0 Then
'Does nothing if the date is the same
Else
I'm trying to write 'user-defined' function in Excel, that doing following:
Getting two arguments: cell and 'like' pattern (_ _ , *, e.t.c).
As a result returns data that match this pattern.
For example:
1. Cell = "golf car is really comfort", pattern = "_ _ _ _" will return "golf"
2. Cell = "toyota is number one", pattern = " num*" will return "number one"
I am trying to read a value in a cell to post in another sheet.
Once the value has been copied, end. However, if the cell to the right of the copied has a value, then that value should be copied instead.
If the cell is blank, then the information should not be copied.
Basically I have a string of data from left to right being populated, with the most current value being copied to a differnt sheet.
I am creating a 'add supplier' userform, in which i open the userform, type in the new suppliers information, then press submit.
The problem is that the data i enter deletes the data that i previously entered into row 26 instead of posting the data into a new row (I wish it to be 27, then 28, etc)
Here is my current code for the userform
VB:
Private Sub CommandButton1_Click()
nextrow = 26
Sheets("Suppliers").Cells(nextrow, 3) = UserForm5.TextBox1.Value
Sheets("Suppliers").Cells(nextrow, 5) = UserForm5.TextBox2.Value
Sheets("Suppliers").Cells(nextrow, 6) = UserForm5.TextBox3.Value
Sheets("Suppliers").Cells(nextrow, 7) = UserForm5.TextBox4.Value
[Code]....
From the attached data is it possible to create a formula to count the number of staff in post that are line managers?
View 7 Replies View Related