I have a list of names in the A column of the sheet. I wish to use the input string, a new name, of a vba box to insert into the column of existing names in alphabetical order by inserting an entire row, which I suspect will be the case anyway. Also 'Return' key doesn't seem to be inserting a CR on the page that I typed this request upon.
View 5 RepliesI have a long list of chemical names that I need to sort alphabetically. A problem arises wherein many of the chemical names are preceded by numbers, as in 1,1,1-Trichloroethane. When Excel sorts the list, it of course groups all entries beginning with 1,1,1- together, then 1,1,2-, and so on, then alphabetically. How can I get Excel to ignore the preceding numbers, and sort only alphabetically?
View 9 Replies View RelatedI have a code of;
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Me.Range("c4:f100")
Dim iSold As Long, iColumn As Integer
Dim strTitle As String
If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub
iColumn = ActiveCell.Column
Select Case iColumn
Case 3
An what i need to happen id the input box saying "Please enter quantity of (whatever is in cell A11) assuming i am clicking on E11
so in other words, in cell A11 it says "MONITOR", i need the input box to pop up and say "Please enter quantity of MONITOR "
On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
I need to sort column I. How do I sort it alphabetically using macro. Will the macro still work if it is inserted in the middle of the code below??
Dim sht As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Name Like "*simple*" Then
'macro to sort column I
End If
Next sht
End Sub
If I sort out the entries in the column customer alphabetically. I want the their respective entries in the further columns to be sorted with them. How do I do that?
Hot Inquiries.xls
I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?
View 2 Replies View RelatedSuppose that i have a table that displays different metrics for each month of the year. I'm dealing with waste management at a thrift store so I'm looking at the weight of donations salvaged, weight of donations sent to landfill and weight sold and so forth. It would look something like this..
weight sent to landfill
weight sold
january
500
450
february
350
390
march
400
789
In my dashboard I'd like to be able to sum the total weight salvaged for certain months like Jan-Feb or Jan-March.
I've named the ranges such as "weightsalvaged.Jan_Feb". So for the sum of the weight salvaged, I have the formula =sum(weightsalvaged.Jan_Feb)
I would like to have another cell where I write "Mar" and the name of the range in the formula automatically changes from
=sum(weightsalvaged.Jan_Feb) TO =sum(weightsalvaged.Jan_Mar)
I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.
There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.
NAME, PHONE, STATE
Bleh, 555-555-5555, =ChkState(B2)
I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field
Function ChkState(pVal As String) As Long
Dim AreaCode As String
Dim StateAbrv As String
AreaCode = Left(pVal, 3)
If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
Else
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function
I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.
Is there any function or methodology to insert text in mid string? ex. I would like to insert a string after the dashes below.
0506-40365503970
0506-40365752746
0506-40365994154
I need to insert 00 if the string of text = 13 in a specific column.
eg. Column K can contain any amount of lines of data. The data will either be 15 or 13 characters in length. If its length is 15 its fine, if its 13 I need to get it to 15 characters.
The first 6 characters are letters so if the length is only 13 I need to add the 00 in after the last letter.
I have a userform with a numerous textboxes, which I have labeled with similar text and a unique number at the end (num1_txt, num2_txt, num3_txt, etc.). I am trying to create a for loop that sequentially references the numbers of the text boxes and pulls the user-entered value in the text boxes into an Excel document.
Here is the code I have so far...
VB:
i = 1
For i = 1 To 18
Dim cyltrack As String
cyltrack = "Me.cyltrack" & i & "_txt.Text"
If Me.cyltrack.Value = "" Then
Cells((cellcount + i), 13).Value = "*"
Else
Cells((cellcount + i), 13).Value = cyltrack
End If
Next i
I am running into problems in trying to convert the string variable, cyltrack, into hard code that I could insert into Me.cyltrack.Value.
i have uploaded a file.....basically i have an input of names and i want to repeat these as shown in the output..is this possible.
View 5 Replies View RelatedI have two columns listing about 400 and 500 names respectively.
First Column has names in alphabetical order and the second column is random.
One column has names in this format: Last name, First name
And the other column lists the names as first name and then last name without the comma
I am trying to see if the list of names in one column exists in the other.
Is there a way to see this without changing the format of the names in each column? If so do I have to erase the comma?
Name Lists (1).xlsx
How can it be that this works?
Sub AA()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub
And this doesn't work?
Sub BB()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
CC
End Sub
Sub CC()
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub
Somehow it seems that the value of the string "ReportFileName" is forgotten when running the subroutine CC. How do I make it not "forget" the file name?
I'm trying to enter text (a staff members initials) into an Input Box so that the initials are then used as the citeria in a CountIF formula entered by VBA
A simplified version of the code I am using is:
Dim staffname As String
staffname = InputBox(Prompt:="You name please.", _
Title:="ENTER INITIALS", Default:="MC")
Range("H9").FormulaR1C1 = "=COUNTIF(RC[-3]:RC[-1],"" & staffname & "")"
The issue is getting the staffname string to be entered within the formula.
What is the correct combination of "" & to enable this to work?
how to specify a file extension type for a browseforfolder part of what i've been doing.
It's not calling correctly as i'm not sure how to feed the inputData string back into the main.
Obviously there's probably better ways of doing this, but can my way be corrected easily?
I've been looking into ByVal and ByRef, but have only seen examples goin from main to the sub and not the other way.
Code:
Sub Main()
Dim strFolderName As String
Dim strFolderTest As String
Dim FRCntrHiLim As Variant
MsgBox "before calling inputbox"
Call InputBoxTest(inputData) '
I have a VBA find and replace question
I have a spreadsheet with cognitive tests (approx 300) that have been given names like
w3_th_e
w4_th_e
dkefs_TMT1_e
pegboard_eh_e
What I need is a VBA macro that replaces the _e only at the end of the string to _ss for those strings that begins with w3_, w4_ and DKEFS_ (e.g. from w3_th_e to w3_th_ss)
Likewise I want to replace only the _e at the end of the string on all other names (e.g. pegboard_eh_e) with _t (e.g. from pegboard_eh_e to pegboard_eh_t) Is this possible?
I'm writing a macro that will automatically change the display in a column of cells.
The input in the cell would be a decimal value, (e.g. 1, 1.25, 1.5, 1.75, 2). As of now, I think the only input options are whole numbers and 1/4, 1/2, 3/4 fractions.
After inputting the decimal value, the cell should update to display the value as the following string format :
Input: 1.25
Displays: 1-1/4"
The purpose being to enable fast data entry while displaying in the desired format.
Here is where I know to begin for the automatic update:
Code:
Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
'
'Code
'
'
Application.EnableEvents = True
End Sub
I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the
Const lngLastPossRow As Long = 65536
Dim strDataRng As String
strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _
strDataRng
While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).
I want to create an input box that will allow you to input the column number it should start in... so instead of it being fixed at A, you can choose column B, C, D all the way to the end if you like.
Sub SplitSheetsNoDelete1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Lmainsheet As String
Dim Lrow As Integer
Dim lcontinue As Boolean
Dim LColamaster As String
Dim lcolatest As String
'Retrieve name of sheet that contains the data
Lmainsheet = ActiveSheet.Name
'Initialize variables
lcontinue = True
Lrow = 2.....................................
Without using VBA, how can i generate strings say
x1
x2
x3
x4
x5
assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.
i did some VBA quite a while back, and picking it up again.
I'm trying to get a function to read in a string, and return a value based on the string value.
Public Function value(ItemType As String) As Integer
If ItemType = " Upper" Then
value = 40
ElseIf ItemType = "Middle" Then
value = 50
ElseIf ItemType = "Lower" Then
value = 25
End If
End Function
When i tried using it at the Cell level, it keep giving error i.e. Invalid Name Error
I have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?
View 7 Replies View RelatedI have Data in Column a There in not demarcation between two Groups of Data
I want it Insert a Row between the two Groups of data.
Each group of Data end with a cell has "Employee" in it
The Row is to be inserted after the cell having “employee”
I have data in 4 columns, A, B, C, D. Each column has 5 rows of data. I want to write a macro to automatically insert 2 columns, after each column in original dataset.
View 8 Replies View RelatedIs there a quick way to copying formula's from one column to every other column? I am using about 1000 columns.
View 6 Replies View Relatedsimple little procedure to insert columns on two sheets and call each by the same name. Macro runs off a command button and inserts a column at E:E and gives the user an input box with which to name the inserted columns.
Code: ...
How in excel do I get a no. to appear in a column dependant on a reference in a different cell. For example If i type March in a cell I want the number 1 to appear in the March column of a table.?
View 9 Replies View RelatedI am having difficulties with sorting my ranges alphabetically. Please see the attached file to see the structure of the data. I have a user form that inserts new rows at the bottom of each month and I need to sort column "A" each time a new record is inserted.
View 13 Replies View Related