Free Microsoft Excel 2013 Quick Reference

Check If Text Contains Certain Characters In Macro Code

Hello, Auto Merged Post Until 24 Hrs Passes;

I have a text file that I import into Excel that is very specific on character numbers.
For example, character 35-49 is used for a specific calculation.

How would I use VBA to check character 34-35 and see if it contains the letters OT or RG and if it contains OT multiple 35-49 by 1.5?

Any help?
I apologize if that was confusing.


Post your answer or comment

comments powered by Disqus
i have a cell i have to check if it contains six characters. I have a list of data that i need to narrow down to six characters. I have successfully done that, but some of the cell has 5, 6, or 7 characters. The list contains about 600 cells, but i don't have time to format them individually. I want to create a formula that returns true or false if the cell contains 6 characters and false if it is above or below 6.

Can somebody help me, i'm new to excel!

Hi all,

Not sure if this is the right thing to do, please advise if not. I created my first thread yesterday, and the responses I got were prompt and very helpful and solved my problem right away.

I have another question now, which is based on the same project, but not what I originally wanted to know, so I thought a new thread was in order.

Here is the link to the old thread:

http://www.excelforum.com/excel-gene...ying-date.html

What I need now, is a macro to ignore certain characters in a range, or if its easier only recognise the characters "h" and "s".

Here is the VBA code that was given to me in the other thread which I am now using in my workbook.

Function IncrementEndDate(rngCalRange As Range, ByVal dQualDate As Date, ByVal dStartDate As Date)
As Date

Dim rngCellLoop As Range
Dim dTmpReturn As Date
Dim dCurrDate As Date

dTmpReturn = dQualDate
dCurrDate = dStartDate

For Each rngCellLoop In rngCalRange.Cells
  If dCurrDate <= dTmpReturn And rngCellLoop.Value <> "" Then
    dTmpReturn = dTmpReturn + 1
  End If
  dCurrDate = dCurrDate + 1
Next rngCellLoop

IncrementEndDate = dTmpReturn

End Function
Here is the worksheet function to go with it:

=INCREMENTENDDATE(<Range of calendar>,<Base qualifying date>,<Calendar start date>)

Can the macro I am asking for be worked into the existing code so I can use the same worksheet function. Or will I need something new altogther?

Attached is the workbook.

You will notice anything entered on, or after the projected qualifying date will not register.

I'm trying to identify if text contained in one cell is also contained in another cell. I used the following formula with some success, but it is not successful in all situations, and I don't know why.

=IF(ISNUMBER(SEARCH(+A1,C1)),"yes","")

Attached is a spreadsheet with the example.

Hi all

Maybe you could help me with an Excel VBA question. I would like to search for a text string to check if it contains certain characters. I'd like to do this in VBA.

Tried using the FIND function, but couldn't get it to work in VBA, and couldn't figure out how to use it with more than one search character.

Any ideas?

Thanks in advance!

Hi everyone! My first time here, hope you guys can help me out...

I need a macro to check if a given range is in between other ranges, something like this:

| Start End | Start End | Start End | ....
Prog. | 123 127 | 145 155 | 179 181 | ....
Exec. | 121 122 | 179 180 | 121 127 | ...
1 1 1

It's the programmed maintenance routine vs the executed maintenance routine of trains. What i need is to check if the executed range fits in between any of the programmed ranges of the week, and if yes then 1 should be the result, else 0.

Basically, I need to check if 121-122 fits in any of the ranges above ( 123-127, 145-155, 179-181 and so on), then do the same for 179-180 ( check if fits in between 123-127, 145-155, 179-181 and so on). Please help, thanks again...

I am trying to see if a cell contains any combination of the letters A,L,P,S, or T. The cell could have one, some or all of the letters. If it contains A, then paste that row on sheet 2. If it contains A and P, then paste that row on sheets 2 and 4. If it contains A, S and T, then paste that row on sheets 2, 5 and 6. Etc. The letters might be in any order, not necessarily alphabetical.

Thanks,
Mark

Need VB code to check if value from Excel exists in Access before importing vaule to Access.
I have data from Excel bing imported into Access from Excel.
Before the data is to be imported into Access I need a safeguard in place.
This would check to see if the data from the excel file(book1) in worksheet (sheet1) exists in the access file (db1) and table (table_1).
What it also needs to do is look for a line match
So what I mean is I need it to look for a row match not just to see if 1 item matches
example does row#16 in the excel file (556, 125, 8) match any rows in access (table_1) (556, 125, 8) in that combination match?
I have attached the files to make things easier

Afternoon all,

I am working on a concept of storing values of variables in macros code on a permanent basis.

Or put in another way, I'd prefer not to have my values obtained from macro calculations to be stored in an excel sheet, as the sheets can be deleted by an uninformed user.

I also need these values to be obtainable at a later stage, thus the storage need to be of a permanent sort.

Is this possible in any way?

Thanks in advance.

Hi All,

I'm looking for a function that can check to see if a cell contains specific text. For Example:

I have many cell that contain some, all, or none of the following 4-character text:
|C180|, |D410|, |S531|, |T844|
and I would like to know if, let's say D410, is contained in a certain cell. I will be checking for this in an array.

I also have another quick question. Is sumproduct or sum(if(.....)) quicker if they are both checking the same amount of criteria?

Thank you in advance,

Jason

Hi All,

I'm looking for a function that can check to see if a cell contains
specific text. For Example:

I have many cell that contain some, all, or none of the following
4-character text:
|C180|, |D410|, |S531|, |T844|
and I would like to know if, let's say D410, is contained in a certain
cell. I will be checking for this in an array.

I also have another quick question. Is sumproduct or sum(if(.....))
quicker if they are both checking the same amount of criteria?

Thank you in advance,

Jason

--
jhockstr

Hello again

How can I check if a cell has certain characters in it?
for example I got 2 cells holding one"Portugal1"and the other "Portugal2"

is there any method which lets me know if these cells hold let's say "Portu"?

by the way, is there a way of doing this considering upper and lower case characters?

Hi!

Is there a way to specify if there are 46 characters in a cell, to apply text wrap, indent the second line, and resize the row height to 25.5?

For example, I always will have text on merged cells B7:C7. I'd like to have a macro that determines if the text goes over C7 (I figured that it would take 46 characters to do this), that the merged cells will be text wrapped, then row 7 will be resized to 25.5.

Please help! I know how to record a macro that will text wrap and resize the row height but am not sure how to do the "if" condition. Thanks much!

I want to check a cell to see if it contain certain text:

EX:
Type Cost
20 oz .08
Bread .10
Package10 0

How do i check to see if type contains the word "package"

Thanks for any help.

Hi all

Currently I have a workbook where there is a sheet called properties import settings. I use this sheet to to keep some of my settings that I can edit on the fly if needed. Quite few of my macros fetch certain settings from this sheet (e.g. settings such as: sheet names, workbook locations, folders where to save stuff, etc).

Now below there is a fragment of my code that essentially picks up some sheet names from named range called name_of_the_sheet_SLOT3 (This named range is a dynamic named range so if I will add some more names to the list it will then automatically expand the named range. Currently it equates "X4:X15", but it will be be growing increasingly as the time goes on). So this code below picks up the sheet names and starts to transfer over information from opened workbook (this opened workbook is defied as wbOpened within code) to back to the original workbook (defied as wbOrig within code). I have included some comments to the code so its bit easier to follow. I know its bit messy.

    Dim ws As Worksheet, sht As Worksheet
wbOrig.Activate ' activate/move back to original workbook

    Dim NameOfTheSheet As Range, Rng As Range
    Set ws = Worksheets("properties import settings")
'----VVV----------named range that contains all the sheet names.-------------------
    Set NameOfTheSheet = ws.Range("name_of_the_sheet_SLOT3")
'Essentially it refers to: Worksheets("properties import settings").Range("X4:X15")
'----AAA---------------------------------------------------------------------------



'<-------THIS IS A SECTION WHERE I WOULD LIKE TO ADD VERIFICATION PROCESS


'----VVV----------LOOPED SECTION. Transfer information over from opened workbook back to the original workbook.
'Essentially it loops through the sheetnames declared in named range "name_of_the_sheet_SLOT3"

wbOpened.Activate ' activate workbook that was earlier opened with macro

    For Each Rng In NameOfTheSheet
        For Each sht In Sheets
            If sht.Name = Rng.Value Then
                sht.Visible = xlSheetVisible
                sht.Activate
                Dim a, i As Long
                With ActiveSheet: a = .Range(.[A2], .Cells(Rows.Count, "a").End(xlUp).Offset(, 14)): End With
                    For i = 1 To UBound(a)
                     a(i, 8) = Format(a(i, 8), "0.0"):
                wbOrig.Activate ' activate/move back to original workbook
                Next: Sheets("imported_Lost_Time").Cells(Rows.Count, "a").End(xlUp).Offset(1,
0).Resize(UBound(a), 14) = a
                wbOpened.Activate ' activate workbook that was earlier opened with macro
            End If
        Next sht
    Next Rng

'----AAA----------END of LOOPED SECTION------------------------------------------------

wbOrig.Activate ' activate/move back to original workbook
wbOpened.Close SaveChanges:=False ' close opened workbook

The code above does its job and works just fine with small exception. If there is a sheet missing that was declared in within the named range it will simply carry on to the next bit. This is fine by the way.

Now the bit where I would like to have some help is to set up a VERIFICATION PROCESS that would check if all the worksheets declared in the named range (name_of_the_sheet_SLOT3) exist within this opened workbook. This should be performed before even trying to transfer information between these workbooks. If it finds any sheets that are missing from the list it should prompt the user with an error message and provide the details of which sheets are missing and then perform "Exit Sub".

I googled and experimented with some codes that I found from the net but I wasn't able to successfully adjust them to my needs. I wont even post my miserable attempts to solve it. It's very embarrassing. I 'm still such a newb when it comes to EXCEL!!!!

I would normally post example sheet but its rather difficult to do it at the moment.
Any help is very much appreciated.
Cheers

Is it possible to use a macro that merges a cell if the cell contains certain text "AWAY", I want it to automaticaly select the cells within range $B$5:$U$42. So if $B$5 contains "AWAY" I want it to merge (b5,b6,c5,c6)

I am looking for help finding a script that will check a cell to see if it contains the : character then change that cell to number format hh:mm

Thanks in advance!

Hey all,

I'm pretty new to VBA so this may seem like a super n00b question but I gotta ask it anyway...

Here is what I want to do (in VBA):

If A2 contains (any)text then concatenate text in A2 with text in C2 and display result in D2, else do nothing ...

Also I want to apply the above to the entire column

Thanks in advance for you help!

Cheers

Hi, I'm having a hard time figuring out a solution for this situation. By the way I'm working in VBA macros. I need to check a cell for two things: if its empty or if it has something besides numbers in it. I know how to code the rest of what I need, but I'm just stuck on identifying if the cell has something else besides numbers inside it. Possible entries would be "Isn't an enhancement" or "NOT A SIR", but I can't be sure those are the only things so I just generally need to check if there are any letters in that cell or not. One thing to note is that the contents of the cells are in text format and not number (the cell has a little green arrow in the top left that tells me about it). Any help would be appreciated, thanks in advance! Oh, just remembered, the number has to be 5 numbers long so I thought of using the Len() function but that doesn't cover things that are words under 5 letters like "None".

I have a table with some functions that returns a text; "Buy" or "".
I would like to check rows in a column to check if any (one or more) of the
functions has returned "Buy". Any suggestions on what kind of function I
should use?
--
Peter B
Norway

Hi
Iam trying to find a formula that will search the beginning part of a cell, for a certain piece of text, and then counting the result if criteria in another column is true.

I have used the sumproduct formula before, but this only works if the cells specifically contain the text that is searched for.

=SUMPRODUCT(($B$2:$B$72="N/R")*($C$2:$C$72=1))

the data that I have has the "N/R" at the beginning of each cell eg Cell B8 = N/R BLACK DRAWING PAD or some of the text looks like this eg Cell B12 = (N/R)POSTER PAINT NEON

so what I want the formula to do, is search column B for any cells that have N/R or (N/R) at the beginning. then count if it matches certain data in column C

any help will be much appreciated

Dear users,

I'm trying to write a code to check if cells in a specific row contain values. If they contain values the next sub will be played otherwise there will be a messagebox and it will exit the sub.

Below a short version of the code i'm trying to write. in Column C from row 7 each cell has a text. Some cells contain the value 'New'.
Somehow the code below doesnt work and I don't understand why. It should find the value New and if the value is found the messagebox will appear. But if this already wont work then there's no point for me in extending the code.

Can anyone tell me what i'm doing wrong?
Sub Check_Fields()
Dim MyCell As Range, MyRange As Range, r As Long
Set MyRange = Range("C7:C1000")
    r = 7
        
        For Each MyCell In MyRange
            If MyCell.Value = "New" Then
MsgBox ("Please fill in all required fields")

End If
Next MyCell
End Sub
The code should look something like this
Sub Check_Fields()
Dim MyCell As Range, MyRange As Range, r As Long
Set MyRange = Range("C7:C1000")
    r = 7
        
        For Each MyCell In MyRange
            If MyCell.Value = "New" And Range("G" & r) <> "" and Range ("H"
& r)<> "" Then
MsgBox ("Please fill in all required fields")
exit sub
else
End If
Next MyCell
End Sub


I have a spreadsheet that I created that we use as our Invoice. I have created a macro button that does certain checks/balances when it is pressed, after the invoice has been created. One of the common problems was that the employees would forget to put a date on the invoice. So now, I have the code for that:
If Range("D5").Value = "" Then
     Range("D5").Select
     If MsgBox("You Forgot to Enter Date!", vbCritical) = vkOK Then Exit Sub
End If
But now, I want to go one step further. In addition to checking to see if there is somthing in the field, I want it to check to make sure that the date entered is TODAY and if it's not, to popup with a MSGBOX that says, "Are you sure you want to enter a date other than today?". If they enter yes, the code proceeds with the checking. If they enter No, I want the program to stop running and to go to that field D5 and wait for the user to correct the date. As a newbie, I'm just unclear what function to use. I've tried various things, all to no avail. Can anyone help me? Thank you!

Hi,

I need help from you experts on this problem I'm having:

I need a macro to go through the values in column A in the "reference" tab and check if the value already exist in column B in "master" sheet. If it does, then copy respective value from column C and paste it to column F in the "master" sheet. If it doesn't exist then add column A and B values to the end of the table in "master" sheet and add column C value to "master" column F. Go through all the values in column A in the "reference" tab.

Thanks in advance for your help. I've attached another sample workbook for this.

Please help

Hi,

I need help from you experts on this problem I'm having:

I've attached a sample workbook with the post.

I need a macro to go through the values in column A in the "reference" tab and check if the value already exist in column B in "master" sheet. If it does, then do nothing for that cell and continue on to the next cell, if it doesn't exist then add all three values for that row (column A, B and C) to the end of the table in "master" sheet and then go to the next cell. Go through all the values in column A in the "reference" tab.

Please help.


No luck finding an answer? You could always try Google.