Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Find then next Results

Well here goes.....I have been tearing my hair out trying to find a way around the query I have.
I am having to design this is Excel 2003.
I have a worksheet of information with is broken down into columns as follows:
Office, Zip Code, Manager Target Date P1-5 (5 columns 1-5), Actual date P1-5 (5 columns 1-5), Current Status P1-5 (5 columns 1-5)

In the current status columns there is conditional formatting on the current status columns turning the cell red, amber or green with the text "on time", "delayed", "failed". This is dependent upon the actual date against the target date using an IF statement.

I have populated the worksheet manually with approx 40 records grouped together by manager.

I have designed a Userform to populate each of columns -which appears to work. However, I have designed it to find the next empty row and populate the information from the Userform. What I would prefer is for the information to be taken from the user form and grouped by manager.

Additionally, when the userform data is taken to the worksheet , the new row does not contain the conditional formatting as per the other populated rows. How can I programme so that when the userform info is added to the worksheet, the cells with the "on time" are then populated with the formula that calculates whether the project is "on time" etc.

Finally,....yes it is a long query......with the user form, I wanted to add the option to search the worksheet as well as the current action of entering info. The user would enter either office, zipcode or manager details and then the search would return the info populated in the fields which could be edited and then the updated info returned to the workbook but not as a new entry.

Thank you for any help that can be provided

This seems very basic so I'm sorry if this is covered other places but I can't seem to find the right words to find the solution!

I have a spreadsheet with several groups of data, and each group has two headings that are "Net". I want the first heading along with the data right below it to go into column B of its current row, and the second one to go into column C, and then loop this process so it will do this for the next data group and so on until it finds the last one. This is what I have so far, but I don't know how to write a Loop that will go until it find the last "Net". Or if anyone has a different solution I am certainly open to that.


	VB:
	
 
Sub FindUntilNoMore() 
     
     
    Cells.Find(What:="net", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Activate 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Cut 
    Range(Cells(Selection.Row, 2).Address).Select 
    ActiveSheet.Paste 
    ActiveCell.Select 
    Cells.FindNext(After:=ActiveCell).Activate 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Cut 
    Range(Cells(Selection.Row, 3).Address).Select 
    ActiveSheet.Paste 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks!
Alex

Hello -
I am comparing two different excel files. I am looking at numbers in one column, and upon finding the same number in another sheet, I want to compare columns in different locations on each worksheet.

Thus far I've just written the code to find the numbers and color the cells in the original range accordingly. How do I select the cell in which the desired value in the second range is found for the Find function? That way I could use the offset and compare the next column.


	VB:
	
 Range("PPG").Rows.Count 
     'No matching value found for PPG in planner matrix
    If Range("PlannerPPG").Find(Range("PPG").Offset(i, 0).Value) Is Nothing Then 
        Range("PPG").Offset(i, 0).Interior.Color = RGB(255, 128, 128) 
         'Value found
    ElseIf Not Range("PlannerPPG").Find(Range("PPG").Offset(i, 0).Value) Is Nothing Then 
        Range("PPG").Offset(i, 0).Interior.Color = RGB(153, 204, 0) 
    End If 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in advance!

I know someone is going to come up trumps with the answer!

I have a userform where people enter data, then that data is entered into the next empty row on the worksheet.

I've worked out the code as below;


	VB:
	
 CommandButton12_Click() 
    Dim iRow As Long 
    Dim ws As Worksheet 
    Set ws = Worksheets("Data") 
     'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _ 
    .End(xlUp).Offset(1, 0).Row 
    ws.Cells(iRow, 3).Value = Me.txtrms1.Value 
    ws.Cells(iRow, 4).Value = Me.txttimeon1.Value 
    ws.Cells(iRow, 5).Value = Me.txttimeoff1.Value 
    ws.Cells(iRow, 6).Value = Me.txtcomments1.Value 
    ws.Cells(iRow, 3).Value = Me.txtrms2.Value 
    ws.Cells(iRow, 4).Value = Me.txttimeon2.Value 
    ws.Cells(iRow, 5).Value = Me.txttimeoff2.Value 
    ws.Cells(iRow, 6).Value = Me.txtcomments2.Value 
    ws.Cells(iRow, 3).Value = Me.txtrms3.Value 
    ws.Cells(iRow, 4).Value = Me.txttimeon3.Value 
    ws.Cells(iRow, 5).Value = Me.txttimeoff3.Value 
    ws.Cells(iRow, 6).Value = Me.txtcomments3.Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here's the problem - what I want the form to do is to enter the data from the 'txt1' boxes into the first empty row and then the data from the 'txt2' boxes into the next row, after the '1' data, and so on.

I can't work out how to do this - can anyone help?

Hi,

Ok imagine i have the following row of values:

6 4 1 3 10 8 9 7 2 5

Imagine I have a (K,L) strategy whereby K = 4 and L = 2.

This means that I look into the first 4 cells and find the highest value, which in our case is 6.

Then, I look at the remaining cells within the row and I need to select the Lth value that is bigger then the value i observed within the first 4 cells, which is 6.

HEre for example, we observe that the 1st value that is higher than 6 within the remaining cells is 10. But since we have L = 2 I want to look for the next higher value, that is both bigger than 6 and 10. Since it does not exist I select the value of the last cell of the row.

How would you code this?

Thanks

Can anyone help?

Thanks

Hi everyone,

Ok I'm having a hard time doing something in VBA.

I have attached a spreadsheet in order for you to understand what I'm doing.

If you look at the far right of the sheet you will see that I have a series of combination of K and L values.

K is the number of cells I ignore, going from left to right, within the range Range(Cells(Row, 8), Cells(Row, 17))

Within those K cells I find the highest value. This value is then put into the largest tonnage T table.

L is the Nth largest value larger than the largest value found above.

Now if I have a (K,L) combination, I need to find the Nth largest value within the range (Cells(Row, 8 + K), Cells(Row, 17)). If that largest value does not exist then I directly select the value in the last cell thus Cells(Row, 17)

For example

(considering a (1,2) combination and just looking at the first row)

If K = 1 and L = 2, then I need to find the 2nd largest value bigger than 8 within Range(Cells(Row, 8 + K), Cells(Row, 17)) which is 10. I need then to put that value into Cells(10,30)

If we have the same combination and we look at the 14th row in the Order in Size table, we see that the largest tonnage we obtain is 9. However since the second largest value within the rest of the Range(Cells(Row, 8 + K), Cells(Row, 17)) does not exist, then I need to select the value of the last cell within that row, thus Cells(Row, 17) and put that into Cells(10, 31)

I have written the following but I do not know how to code in the idea of getting the second largest value AND If I use the combination say (1,2) I need to put the values that I find in row 10. Since I have 50 Order in Size rows I would therefore have 50 values in row 10 starting in column 30.


	VB:
	
 
 
Sub BlowUpSub() 
     
    Dim K As Double 
    Dim L As Double 
    Dim N As Double: N = Cells(14, 4).Value 
    Dim Row As Double 
    Dim BlowUpChoiceOrder As Range 
    Dim Count As Long 
     
    For Row = 9 To 58 
        For K = 1 To 8 
            For L = 1 To N - K - 1 
                Set BlowUpChoiceOrder = Sheets("Sheet1").Range(Cells(Row, 8 + K), Cells(Row, 17)) 
                Count = WorksheetFunction.Count(BlowUpChoiceOrder, BlowUpChoiceOrder.Value > Cells(Row, 18 + K).Value) 
                If Count = 0 Then 
                    Cells(Row, Row + 21).Value = Cells(Row, 17).Value 
                Else 
                    Cells(Row, Row + 21).Value = Cells(Row, 18 + K).Value + Cells(Row, 29).Value 
                End If 
            Next L 
        Next K 
    Next Row 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have an array of values arranged in a block of cells, with the left column containing depth values (attached example).
I would like to trace (find) through the array a particular value and return the positions (depth) for the same value in the next nearest cell in each successive column.
Eg. in the example set;
start with the value of 2.2 (at 4292.6) then move to the next column and find the adjacent or nearest same value (up or down) and return its depth, or interpolated depth if not an exact value match. So 2.2 in the next column would be between 2.15 and 2.35 (a quarter of the way between them), so the correct depth to return would be; 4292.6-((4292.6-4292.5)/4) = 4292.575

Is it possible to somehow initially select the value to be traced through the array and then use an 'intelligent' function that could find where the initial value lays in terms of depth in each column, and return these depth values?

My previous post re coloring cells according to value is to enable fast visual recognition of patterns (arcs across the array) of similar values.

Thanks,

Derick

Hi All,
Please help...
I.am very beginner
and only 3 mont ago learning english language, sorry.

So i building little excel userform.
This is userform included 5 textbox, and 1 commandbutton.
if click commandbutton, then one little macro runnig. this is macro copy date from textbox and paste worksheet next empty row...
This is macro detail:

	VB:
	
If Trim(Me.txtPart.Value) = "" Then 
    Me.txtPart.SetFocus 
    MsgBox "Please fill in all fields" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is macro only my pc runnig :S
I running this macro other pc and next error messages visible:
Compile Error: Can't find project or library..
I try uncheck, and check all references.. and try again and again error messages visible

Many thanks in advance
Robert (from hungary)

Hi there,

This is my first post, so I hope I'm adhering to the rules. I'm also fairly new to VBA, so please be gentle.

The attached screenshot might help. I'm trying to create a spreadsheet to update daily, whenever our market intelligence arrives by email. I'm not trying to write a macro which can select a range of cells (G:L) relative to the date in Column B, which represents the value in cell P6.

In other words:
Read the value of cell P6Find that value in column B (e.g. B646)highlight the cells in columns G through to L on that row (e.g. select G646:L646)
I am then aiming to paste the values in that range of cells, so that those stay in the spreadsheet and are not lost when the next lot of figures comes in the following day.

So far I have:

	VB:
	
Range("G646:L646").Select 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 
ActiveWindow.SmallScroll Down:=12 
Application.CutCopyMode = False 
ActiveWorkbook.Save 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but that's obviously a static range, rather than looking up the date first.

Please help. Thanks,
Donnie

Hi All,

I am trying to write a macro to clean up a CSV file which automatically hard codes titles throughout the spread sheet. I am getting an error on the "FindNext" method below. I am getting "Unable to get FindNext propety of the Range Class" error. Also, the code for the For loop for multiple lines does not appear to be working. Any help would be greatly appreciated.


	VB:
	
 cleancsv() 
    lastRow = Range("A1").End(xlDown).Row 
    pmpt = InputBox(Prompt:="What text are you looking for?", _ 
    Title:="Text", Default:="i.e.: Finished Goods Inventory") 
    numrows = InputBox(Prompt:="How many rows to delete (counting original):", _ 
    Title:="Number of Rows", Default:="i.e.: 1") 
    Set cell = Range("A1:A" & lastRow).Find(pmpt) 
    If Not cell Is Nothing Then 
        For l = cell.Row To cell.Row + numrows 
            Range("A" & l).EntireRow.delete 
        Next l 
    End If 
    Do 
        Set cell = Range("A1:A" & lastRow).FindNext(cell) 
        If Not cell Is Nothing Then 
            For j = cell.Row To cell.Row + numrows 
                Range("A" & j).EntireRow.delete 
            Next j 
        End If 
    Loop Until cell Is Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:

12345 a
12345 a DUPLICATE
11111 b
23123 b

Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?


	VB:
	
 MarkDupes() 
     ' Marke Duplicate Rows with "Duplicate"
     ' Macro7 Macro
     ' Macro recorded 6/9/2008 by shamad
     '
     
     '
    x = ActiveCell.Row 
    y = x + 1 
     
    Do While Cells(x, 1).Value  "" 
        Do While Cells(y, 1).Value  "" 
            If (Cells(x, 1).Value = Cells(y, 1).Value) Then 
                Cells(y, 3).Formula = "Duplicate" 
            Else 
                y = y + 1 
            End If 
        Loop 
        x = x + 1 
        y = x + 1 
    Loop 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks

I'm building a course registration workbook and am working on the unregistration macro.

I have a seperate worksheet for each course, labelled by the course code.
There is also a page for each registered participant that shows what courses they're in.

The macro reads off the information on the participants page, and then should goto each registered course's page, look in column C for their name, find it, delete the contents of that row for Columns C to G (has their other information) and then shift all CELLS (not rows) below the now deleted cells up 1 to keep track of the order they were added.

I've tried recording my own macro and editing... but it doesn't work very well for this.
I've also tried searching and come up with nothing, but if someone can look at my code, and/or point me in the right direction, it would be greatly appreciated.


	VB:
	
 
For Each ws In Worksheets 
     'checks only course sheets that match the courses the individual is registered in.
    If ws.Name = unregisterCourse1 Or ws.Name = unregisterCourse2 Or ws.Name = unregisterCourse3 Or ws.Name =
unregisterCourse4 Or ws.Name = unregisterCourse5 Then 
         
         'Should search for the variable "unRegister" in column C and select it.
        Cells.Find(What:=unRegister, After:=ActiveCell, LookIn:=xlFormulas, _ 
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False).Select 
         
         'Now needs to select the four cells to the right as well (C:G) and delete them
         
         'Select all cells below from C:G and shift them up one.
    End If 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a userform that has two comboboxes to search data on a sheet and a textbox to input data to a specific cell on the same sheet. The cell that the textbox writes to is determined by the combination of choices selected in the comboboxes. The comboboxes are searching as required but I don't know how to determine which cell to write my data from the textbox to. I'll attempt to clarify...combobox1(drivename) searches the data in column C, combobox2(unconformlist) searches the data in column H. The combination of these determines a specific row and I need to write data from the textbox(initials) into this row in column M.
Any help is much appreciated.


	VB:
	
 CancelButton_Click() 
    Unload Me 
    Sheets("Index").Select 
End Sub 
 
Private Sub initials_Change() 
    initials = UCase(initials) 
End Sub 
 
 
Private Sub OKButton_Click() 
    Dim ws As Worksheet 
    Set ws = Worksheets("QAQCconformity") 
     
     
     'copy the data to the database
    ws.Cells(1).Offset(2, unconformlist.ListIndex + 12) = initials.Value 
     
     ' Check that each combo box contains a value
    If drivename.ListIndex = -1 Then 
        MsgBox "Please select a dive name" 
        drivename.SetFocus 
        Exit Sub 
    End If 
     
    If unconformlist.ListIndex = -1 Then 
        MsgBox "Please select an Uncomformity" 
        unconformlist.SetFocus 
        Exit Sub 
    End If 
     
    MsgBox "Do you need to to sign off another Unconformity", vbYesNo 
    If Answer  No Then 
         'Code for No button Press
        Unload Me 
        Sheets("Index").Select 
    Else 
         'Code for Yes button Press
        drivename.SetFocus 
    End If 
     'Finish and close form
     
End Sub 
 
Private Sub UserForm_Activate() 
    With unconformsignoff 
        .Top = Application.Top + 200 '< change 100 to what u want
        .Left = Application.Left + 211 '< change 211 to what u want
    End With 
     
End Sub 
 
Private Sub drivename_Change() 
     
     '   Clear other comboboxes
    Me.unconformlist.Clear 
     
     '   Load unique values for selected value in first combobox
    For Each cell In Worksheets("QAQCconformity").Range("C3", Worksheets("QAQCconformity").Range("C3").End(xlDown)) 
        If cell.Offset(0, 0) = Me.drivename Then 
            If cell.Offset(0, 0)  cell.Offset(1, 1) Then Me.unconformlist.AddItem cell.Offset(0, 5) 
        End If 
    Next cell 
     
     
End Sub 
 
Private Sub UserForm_Initialize() 
     
     '   Load unique Drivename values into first combobox
    For Each cell In Worksheets("QAQCconformity").Range("C3", Worksheets("QAQCconformity").Range("C3").End(xlDown)) 
        If cell.Offset(0, 0)  cell.Offset(1, 0) Then 
            Me.drivename.AddItem cell 
        End If 
    Next cell 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Ok,

Basically this is what I want to do:
1. Search a specific column (Column 21/U) for non-blank values in Worksheet 1
2. Copy the entire row containing the non-blank values
3. Paste these rows into Worksheet 2.
Repeat steps 1-3 an additional 2 times, where Worksheet 1 is always searched but one more column to the right (ex. Column 22/V) is the target column for the search, then the rows are pasted into the next Worksheet (for ex. Worksheet 3)

Thanks a lot!

I am creating this VBA Script with excel to take a series of identifier values from a column in one sheet (sheet3) and attempting to match them to a column (6, or F) in sheet2 then extract a string from yet another column (2, or B) in sheet2 located on the same row. I am using the find() method to match the identifier values.


	VB:
	
 
    Dim k As Boolean 
    Dim r1 As Integer 
     
    Sheet2.Range("F:F").Find(Sheet3.Cells(i, 1)).Activate 
    k = False 
    Do While k = False 
        If ActiveCell = Sheet3.Cells(i, 2) Then 
            k = True 
        Else 
            r1 = ActiveCell.row 
            ActiveSheet.Range("F:F").FindNext(Cells(r1, 6)).Activate 
             
        End If 
    Loop 
    Egg_Extract = Sheet2.Cells(ActiveCell.row, 2) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When running this script the line:

	VB:
	
Sheet2.Range("F:F").Find(Sheet3.Cells(i, 1)).Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
gives: runtime error 91 - Object variable or With block variable not set

Why is this happening? And How can I fix it?

Thanks in advance for your help

Hey!

I´m quite new to vba and excel but I´m trying to learn by creating a dvdlist over my films using excel and vba. I have a sheet for each movie category and one column per sheet. In that sheet I have the movietitles for that specific category. each title have a commentary which include actors, directors, length, language and subtitles. I know I should have created a database, but I wanted to try this one out first

I have one problem though I have created a find function that allows me to search and find a specific movie title. The problem is that if I for example have 3-4 different movie titles that include for example "robin hood" and they are all spread on different sheets. The find function then only finds the first one, it won´t look for the rest... even if I push the search button again on my userform that I created. How do I make it search on the other sheets as well for the same movie title?

My code looks like this: (film.Text is where I enter the specific movie title on my userform)

	VB:
	
 film_Click() 
     
    Dim datatoFind As String 
    Dim c As Range 
    Dim counter As Integer 
    Dim currentSheet As Integer 
    Dim rFound As Range 
     
    kategori.Text = " " 
     
    If film.Text = " " Then 
        Exit Sub 
         
    End If 
     
    datatoFind = film.Text 
     
    If datatoFind = "" Then Exit Sub 
     
    For Each sh In Worksheets 
        Set c = sh.Columns(1).Find(What:=datatoFind, After:=sh.Cells(1, 1), LookIn:=xlFormulas, LookAt _ 
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
        False) 
        If Not c Is Nothing Then Exit For 
    Next sh 
     
    If Not c Is Nothing Then 
        sh.Activate 
        c.Activate 
        If film.Text = film.Text Then handling.Text = c.Comment.Text 
        If film.Text = film.Text Then kategori.Text = Range("A1") 
    Else 
        MsgBox "Det hittas ingen film med den titeln. Kontrollera stavningen eller sök efter ny filmtitel" 
    End If 
     
     
    kategori.MultiLine = True 
    handling.MultiLine = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Just a quick question here. I've got a bit of code for searching a sheet for a particular value in a cell and selectiing that cel that I got off of this site. This is the pertinant piece of code:


	VB:
	
 
Dim SR As Integer 
Dim ER As Integer 
Dim SC As Integer 
Dim EC As Integer 
Dim found As Boolean 
CancelDate = InputBox("Old Date-Time Number?") 
If CancelDate = vbNullString Then Exit Sub 
SR = 1 
ER = 1000 
SC = 1 
EC = 12 
found = False 
For RowVar = SR To ER 
    For ColVar = SC To EC 
        If Not found Then 
            If Cells(RowVar, ColVar).Formula = CancelDate Then 
                Cells(RowVar, ColVar).Select 
                found = True 
            End If 
        End If 
    Next 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My question is: can you not do more than one of this kind of search in a macro? The first search I have of this type goes off fine, but the second and the third don't actually search the sheets. They do all the subsequent actions just fine but do them from whatever cell is selected in the sheet when it switches to that sheet, rather than the string I'm searching for. Any help would be greatly appreciated.

Hi All!

Got a little problem using the setfocus method. Please find below a screenshot of my userform (Sorry, it's mostly french), and a code snippet of the problem I have.


	VB:
	
 TextBox6_Enter() 
     
    If TextBox5.Value  "" Then 
         
        If DoesInvoiceExist(TextBox5.Value) = False Then 'Validates if Invoice No is valid
             
            MsgBox "Aucune facture de ce numéro pour ce client," & Chr(13) & Chr(10) & _ 
            "vérifiez le numéro de facture et recommencez!", vbOKOnly, "Gestion Épidaure Inc." 
             
            TextBox5.Text = " " 'Erase contents of TextBox5
            TextBox5.SetFocus 'Return to TextBox5
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My problem is, on TextBox6_Enter(), it does check up, and display the MsgBox regarding the invoice No, but then it sets the focus to TextBox7 instead of TextBox5...

Any Ideas why? Auto Merged Post Until 24 Hrs Passes;

Auto Merged Post Until 24 Hrs Passes;

Ok, found out where I went wrong... Instead of using the TextBox6_Enter() event, I used the TextBox5_Exit Event... Code as follows:


	VB:
	
 MSForms.ReturnBoolean) 
    If TextBox5.Value  "" Then 
        If DoesInvoiceExist(TextBox5.Value) = False Then 
            MsgBox "Aucune facture de ce numéro pour ce client," & Chr(13) & Chr(10) _ 
            & "vérifiez le numéro de facture et recommencez!", vbOKOnly, "Gestion Épidaure Inc." 
            TextBox5.Text = vbNullString 
            Cancel = True 
            Exit Sub 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
All thanx to Dave's code on the following URL:
http://www.ozgrid.com/forum/showthread.php?t=62115

Hey all,

My question is, instead of deleting the row, how can I use the combobox to replace that row with the updated info rather than delete and resort?

I have a combobox that selects names from a sheet, column A and populates itself on Userform activate/initalize. Using the Combobox to select a name, this code below populates all the fields on the form, various text and comboboxs.

When users hits the update button, it currently finds the row and deletes it, see second code example, but this reaks havoc on various parts of the program, I have to move the combobox and add name textbox's because when it deletes the row, the combobox takes on the next rowsource and then writes that info, rather than the info selected.


	VB:
	
 ComboBox1_Change() 
     
    If bBlockEvents = True Then Exit Sub 
    If ComboBox1.Value = "" Then 
        Reset 
        bBlockEvents = True 
        ComboBox1.ListIndex = -1 
        bBlockEvents = False 
        Exit Sub 
    End If 
    userow = ComboBox1.ListIndex + 3 
    usercolumn = 1 
    If userow = "0" Then 
        ComboBox1.Value = "" 
        Reset 
    Else 
        Special 
        TextBox1.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn).Value 
        TextBox8.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 1).Value 
        TextBox2.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 2).Value 
        Me.TextBox2.Value = Format(Me.TextBox2.Value, "DD-MMM-YY") 
        TextBox3.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 3).Value 
        Me.TextBox3.Value = Format(Me.TextBox3.Value, "DD-MMM-YY") 
        TextBox4.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 4).Value 
        Me.TextBox4.Value = Format(Me.TextBox4.Value, "DD-MMM-YY") 
        TextBox5.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 5).Value 
        Me.TextBox5.Value = Format(Me.TextBox5.Value, "DD-MMM-YY") 
        ComboBox2.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 6).Value 
        TextBox6.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 7).Value 
        Me.TextBox6.Value = Format(Me.TextBox6.Value, "DD-MMM-YY") 
        ComboBox4.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 8).Value 
        TextBox7.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 9).Value 
        Me.TextBox7.Value = Format(Me.TextBox7.Value, "DD-MMM-YY") 
        ComboBox3.Value = Worksheets("EPR Tracker").Cells(userow, usercolumn + 10).Value 
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

This code is within a command button click sub.


	VB:
	
NameOrig = TextBox1.Value 
 
 'Finds the name from the dropdown and deletes it
With Worksheets("EPR Tracker").Range("A:A") 
    Set c = .Find(NameOrig) 
    If c = c Then 
        c.EntireRow.Delete 
    End If 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Then below that, I add a line to the worksheet, dump in the info (which changed to the next row down because of the deleting the row) and then re-write it to that empy line, then resort.

I know there has to be an easier way.

Thanks in advance for any help!

Joe

Good day fellow Ozgrid,

I have another challenging solve for a VBA macro. So here it goes, as I have become frustrated trying to make an array formula with no joy. On my sample worksheet provided below this is what I am trying to accomplish:

(Solution cells) B4:I4 looks to the (Combination cells) M5:R10 for a match
If a match is present then cell J4 gives a "win" ,
If no match then cell J4 gives a " lose ".

Next if a win is present in cell J4, then cell K4 looks for when the draw number that matches occurred on from cells A4:A10, then subtracts the two(e.g. solutions cells from combination cells) to give the actual " # of draws to a win ". If no win is present in cell J4, then the default is zero for cell K4

The formula would be copied down thru cells J4:K12

Please refer to sample worksheet attached so that you can understand more clearly of just what I'm trying to do.

Thanks again in advance.


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