I'm getting this weird error which must because I've overlooked some simple thing about formatting (is it called formatting?) my variables.
See, what I think is wrong is that I'm comparing a string with a range and that can make 37 not equal to 37.
Because that is my problem; I have an If statement that compares 2 variables that I know should be the same but the If statement doesnt think so. So to my question; am I on the right track here and if so, how should I tell vba what is a string and what is a range and if I am on the wrong track here;
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
" A B C D | SUMMARY model qty| modelqty 1 4.12922.0000| 4.12952.2000 2 2.000012.1250| 2.000025.1250 3 4.12929.0000| 318.0000 4 318.0000| 5 4.1291.2000| 6 213.0000| "
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
For example: I want to sort through a a bunch of textboxes and refer them to certain cells. I know I can have a string "TextBox" and then the looping number "nbr" and merge them into for instance TextBox1. But I can't figure out how to use this as a command. Like
Different formula in different cells give me some text as output, to format this. I would like to copy and paste in one cell, so that I can easily then register a macro on top to wrap the text in one big cell.
I'm trying to insert a set of strings, and for some reason its not going to the next open row, its just inserting them over the last record.
Code:
Private Sub Insert_Payment_Click()Dim Payment As String, PaymentAddress As String Worksheets("Payments").Select Payment = Range("E4") PaymentAddress = Range("B4") Worksheets("RentalDates").Select Worksheets("RentalDates").Range("I1").Select If Worksheets("RentalDates").Range("I1").Offset("1,0") "" Then Worksheets("RentalDates").Range("I1").End(xlDown).Select
I am trying to parse employee names into seperate string variables in order to display surname, first name and middle name in seperate fields on a form.
The format of the full name is "Surname, First Name, Middle Name (If any)
I have managed to deal with surname and firstname but I am stumped on middle name. Not all employees will have a middle name but for those who do, I need to parse it.
I am trying to compare two cells to see if its two values are the same. Some of the cells have a space in between numbers or a symbol like "-"
Ex.
one cell's value is 1213180IN
and the other cell's value is 1213180-IN
the program I have written marks these as different because of the "-" in the second cell....is there a way, maybe with a string function to overlook that "-"?
I've got above basic VBA knowledge and learning very quickly (largely due to great forums like this) I'm now creating a userform for the first time. I'm trying to create a New Supplier form and one of the fields is going to be for Supplier ID where it takes the first 6 letters of the Supplier name (entered by the user) & tags on '001' at the end. I've managed to do this but I would like some code to search existing supplier ID's ( Column A on separate sheet) and check whether the ID created already exists and if it does add on '002' instead of '001'.
SO for example..
If user creates a supplier called Microsoft the supplier ID will be Micros001 - this I can do, but if Micros001 already exists then it will generate an ID Micros002, Micros003 and so on...
In the attached file, I have variable range in column A:B, column C:D and in column E:F
I want a macro to do the following:
Start with sheet "A", select the available range in column A then copy and paste in the sheet "B" but with all the cell values added with the value in H1.
Then in sheet A, simply copy the available range in column B and paste it in sheet B
Do the same until column F in sheet A. Pastespecial if it is odd column. simple past it is even column.
I know the macro code for the simple paste. But I am struggling with the paste special code.
I have a problem, I have a workbook that is used for importing products on to my website but for the description field it keeps adding the double quotes to the text. this text is HTML code. when I import the products to my website, these quotes show up at the beginning and end of the description. You can't see the quotes in excel but if you copy and paste the cells data in to a text editor you will see the quotes.
My question is how can I remove these? I have removed them in the text editor but as soon as the data goes back in to excel the quotes are automatically inserted again...
I have a long list of locations in an excel spreadsheet, stating either the country (eg India) or for US locations the state (in a two letter format, eg NY). I need to change all the state location to say "USA". How can I do this, without doing 50 find and replaces? Can I do something based around the fact all the USA locations are two letters, whereas all the others are longer?
I don't know what's wrong with this it's not working..
=COUNTIF(SEARCH("radio",A2:A20),"")
i just want to count all the cells from a2 to a20 that has a word "radio" from that cells. Since a2 until a20 have different words like tv power, radio blaster (this should be consider because there's a radio word on it) and so on.
if i use only =COUNTIF(A2:A20,"radio") the word should be exact "radio" and it wont consider the word "radio blaster".
I have a column filled with names per row. I wanted to standardize it by using the following format.
The comma after the last name is always an indicator to me that it is the last name.
<First Name> <Middle Initial(if any)> <Last Name>
My actual records contains the following:
BIMBO EPPING - OK AHMAD SADICK - OK Salana, Laura R - need to be arranged to > Laura R Salana Cantos, Rhoda Grace J - need to be arranged to > Rhoda Grace J Cantos
I'd like to write a formula that check if a string (contained in another cell - say A1) includes at least one occurrence of one of a set of 5 sub strings. If the substring is included then it should return which one.
For istance, say that the cell A1 = " The colour is BLACK" then I'd like a formula in cell B1 that check if any of the following strings is included in A1 (RED, GREEN, BLUE, BLACK, ORANGE) and that tells me which one.
I need to get the count of a string from multiple columns. The problem is that string1 exists in column A and String2 can exist in one of eight columns. See the example below.
What I want to do is use two search criteria. I want to find the number of times I have Type=A and if the row has a case Beta. In the example above I should get an answer of 2. (From rows 3 and 9)
I tried countifs but I cant seem to get it working properly.
so it first sorts in alphabetical order using the string before the "-" i.e. CAM27 comes before CAM100, and then by the biggest 'secondary values' (the value after the "-") so that COM98-23 comes before COM98-21?
I have a workbook called Keyword that has two columns c,and d, of words.
The length of the columns can vary,because I add words to them.
I also have a workbook called testfile, and cell B1 has a word in it.
Testfile also has an .xla module that adds in when testfile is open.
I want to type a function called asset in cell C1 of Testfile,and have it compare each word in Keyword workbook to the word that's in cell B1 of testfile. If a match is found in column C of Keyword, the function returns the letter C. If match is found in col D of Keyword, it returns the letter D. If no match, function returns "no match".
I have both workbooks open and the vba code I stuck at the bottom of the .xla module as another function.
Here's my code so far.
Function asset(Description As Variant) As String 'arg. is the word in cell B1 in testfile
Dim LastRowOfC As Integer, LastRowOfD As Integer Dim i As Integer
I have a set of 4 columns with YES/NO statements in them. I need to find any YES strings, of three or more, within one column, uninterupted by a YES in another colum.
EXAMPLE: _____A ___B__C__D__E 1 1/1/2008 YES NO NO NO 2 1/2/2008 NO NO YES YES 3 1/3/2008 YES NO NO NO 4 1/4/2008 YES NO NO NO 5 1/5/2008 NO NO NO NO 6 1/6/2008 YES NO NO NO..................
I have 2 sheets of data. I am looking for a macro that will check if there are any strings that exists on Sheet1 but not on Sheet2 and if they do, paste them at the end of Sheet2. All of this data is in Columns A by the way.
