Search results

Try this where the combo box refers to F1 on sheet1 and the data "to appear" is in columns A, B, C, D of sheet2. Regards, Howard Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim colA As Long Dim colB As Long Dim colC As Long Dim colD As Long Dim ColDa As Variant If...

Hi Narayan, Yep, that does it. Thanks a ton. Frankly I find the sound a bit distracting if not annoying. I will offer a 'with sound' and a 'no sound' version to the end user. Thanks again. Regards, Howard

Thanks Hui for taking a look and the suggestion. I made this change and verified no spaces. If c.Text = c.Offset(0, 2).Text Then .... The results are the same missinformation as before. Seems that both IF statements are coming into play for each answer when the IF should disqualify one...

The answers to a quiz are entered in Range("C12:C20"). The answer key is listed in Range("E12:E20") = AA, BB, CC, DD, EE, FF, GG, HH, II. The quiz answers in Range("C12:C20) = WW, BB, CC, DD, WW, FF, GG, HH, II. (Two wrong answers to test code) Run the code: The code says the first WW is...

Hi Hui, The Speak Cells activate directions are clear and gets it done. The OP mentions Speech Recognition, which I believe is different. I muddled about to install Speech recognition, and do have it installed. However, the few times I tried it didn't impress me, to wit: seemed not to be...

Hi =REPT("G",5), Can post a small sample of your data? Post the vlookup formula you are using. Where/what are the dulicates you speak of? Returning the M only from a cell that has M234566 is indeed quite strange. Regards, Howard

Tried your link and got: The link you're trying to access can't be used to share files. Please ask the file owner to provide you with a shared link instead. Contact Box Support if you need help. Regards, H

Maybe something as simple as this. Paste in the sheet module you are conducting the Find. Option Explicit Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Value = "" Then Exit Sub If ActiveCell.Column = 5 Then ActiveCell.Offset(0, 4).Select...

I changed your formula removed ; put in ,. For each ""*$" I put in H1, where cell H1 = $. I entered "all" in each of the cells looking for "all". Formula returned seven $'s. I entered 50 in D4 and formula returned 50$$$$$$$...

Just to add to Luke M's code in case the column A length varies in sheet Data... Sub TransferData() Dim lastCol As Long With Worksheets("Log") lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column 'Check if we need to paste in first col, or next col If .Cells(1, lastCol)...

Try this for a VBA solution. ' Option Explicit Sub ConConWComma() ' ConCol is a named your range of cells in column A ActiveSheet.Range("ConCol").RemoveDuplicates Columns:=1, Header:=xlNo Range("B1") = Join(Application.Transpose(Range(Range("A1"), _ Range("A" & Rows.Count).End(xlUp)))...

Hi bobsri, =LEFT(I1,12)-LEFT(H1,12) returns 5. Regards, Howard

If cell D2 had this in it 1,4,7,10,11 OR 1 4 6 2 12 the two formulas I offered would return 5, the number of digits. I believe you want the number times a particular digit occurs in the various cells. So if you had ten number 12's then that would indicate there were ten no. 12 reasons...

For the cell using comas: =LEN(D1)+1-LEN(SUBSTITUTE(D1,",","")) For the cell using spaces: =LEN(D2)+1-LEN(SUBSTITUTE(D2," ","")) The second formula seems to work if both comas and spaces are used. 1, 4, 7, 10, 11 Regards, Howard Posted before I saw your example sheet. This probably...

Maybe this, I am vague on understanding the question. A AA D DD 444 AA 111 B BB BB 222 C CC CC 333 D DD DD 444 Where the data is in columns A, B, C, D, E, F, G. Where cell D1 returning DD is =VLOOKUP(C1,A1:B4,2,0) Where cell E1 returning 444 is =VLOOKUP(D1,F1:G4,2,0) Regards, Howard

Hi Luke M, I'm bonking on how to use your UDF. It would seem to me =ConcIf(Check_Range) would do it, where "Check_Range" is a named range that I want to Conc. Looks like it will do vert to horiz OR horiz to vert depending on the cells(1,i) or cells(i,1). Thanks, Howard

Hi jacharya, Option Explicit Sub MissNum() Dim c As Range For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row - 1) If c.Offset(1, 0).Value - c.Value <> 1 Then MsgBox "Missing Number " & c.Offset(1, 0) - 1 c.Offset(0, 1).Value = c.Offset(1, 0) - 1 End If Next...

Ah-Ha...! Got it, pretty slick. Thanks, Howard

Hi Deb, What am I doing wrong? I did all this: * Name Manager > New * Name : Color_cell * Refers to : =GET.CELL(63,OFFSET(INDIRECT("A1"),ROW()-1,0)) * Now in B1 Write Formula as =Color_cell Fill A1 red, select B1 click in the formula box then hit enter returns 3. Clear Col A...

You might start here, may or may not suit your needs but Chip Pearson seems to be the color counting guru among MVP's. http://www.cpearson.com/excel/cfcolors.htm If the rows are NOT conditional format colored, it's pretty much duck soup with VBA. Regards, Howard

Here's a shot in the dark... =VLOOKUP(A1,INDIRECT(F1),2,FALSE) Regards, Howard

Hi Gumbles, This is for where the cursor (activecell) is NOT where the button is. ActiveCell.Offset(1, 0).Insert Shift:=xlDown, _ CopyOrigin:=xlFormatFromLeftOrAbove If you for sure you want the insert from where the button is, I don't know. But I will mess around to see if I can make...

Hi, nmorrison You might try this to make entering your data a bit easier/quicker. Copy and paste in the vb editor of sheet Hoja2. I assigned short-cut keystroke to both codes ctrl + a & ctrl + e. Option Explicit Sub PatientAdd() 'ctrl + a Dim PName As String Dim CptC As String PName...