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

Free Microsoft Excel 2013 Quick Reference

Finding the first empty cell Results

Hi guys,

I am getting a NEXT without FOR error message when i try to run this code.

This is the portion of sub im having trouble with.. been trying to work it out but to no avail. I thought that i might have to do Next Ccell, though I got the same error.

 'If Found in Ws1 then copy entire row to Ws4
For Each Ccell In SearchRng
  Set CopyRng = FindRng2.Find(What:=Ccell, LookAt:=xlWhole)
  If Not CopyRng Is Nothing Then
    CopyRng.EntireRow.Copy Destination:=PasteRng
    Set PasteRng = PasteRng.Offset(1, 0)
    Else
        If CopyRng Is Nothing Then Set CopyRng = FindRng4.Find(What:=Ccell.Value & ".", LookAt:=xlPart)
            If Not CopyRng Is Nothing Then
                CopyRng.EntireRow.Copy Destination:=PasteRng
                Set PasteRng = PasteRng.Offset(1, 0)
  End If
Next


This is the entire SUB

 Sub findandmake()

Dim Ws1 As Worksheet 'Search
Dim SearchRng As Range
Dim Ws2 As Worksheet 'Physical
Dim FindRng2 As Range
Dim Ws3 As Worksheet 'Virtual
Dim FindRng3 As Range
Dim Ws4 As Worksheet 'Output

Dim CopyRng As Range 'Set when Found
Dim PasteRng As Range 'keeps the latest row

Set Ws1 = Worksheets("Servers To Find")
Set SearchRng = Ws1.Range("A1:A" & Ws1.Cells(Ws1.Cells.Rows.Count, 1).End(xlUp).Row)

Set Ws2 = Worksheets("Physical Servers")
Set FindRng2 = Ws2.Range("D3:D" & Ws2.Cells(Ws2.Cells.Rows.Count, 4).End(xlUp).Row)

Set Ws3 = Worksheets("Virtual Servers")
Set FindRng3 = Ws3.Range("D3:D" & Ws3.Cells(Ws3.Cells.Rows.Count, 4).End(xlUp).Row)

Set Ws2 = Worksheets("Physical Servers")
Set FindRng4 = Ws2.Range("E3:E" & Ws2.Cells(Ws2.Cells.Rows.Count, 4).End(xlUp).Row)

Set Ws4 = Worksheets("Server Results")
Set PasteRng = Ws4.Cells(1, 1)

'Clear all
Ws4.Cells.ClearContents

'First the Headers
Ws2.Range("2:2").Copy Destination:=PasteRng
Set PasteRng = PasteRng.Offset(1, 0)

'If Found in Ws1 then copy entire row to Ws4
For Each Ccell In SearchRng
  Set CopyRng = FindRng2.Find(What:=Ccell, LookAt:=xlWhole)
  If Not CopyRng Is Nothing Then
    CopyRng.EntireRow.Copy Destination:=PasteRng
    Set PasteRng = PasteRng.Offset(1, 0)
    Else
        If CopyRng Is Nothing Then Set CopyRng = FindRng4.Find(What:=Ccell.Value & ".", LookAt:=xlPart)
            If Not CopyRng Is Nothing Then
                CopyRng.EntireRow.Copy Destination:=PasteRng
                Set PasteRng = PasteRng.Offset(1, 0)
  End If
Next

'If Found in Ws2 then copy entire row to Ws4
Set PasteRng = PasteRng.Offset(1, 0) 'One row empty
Ws3.Range("2:2").Copy Destination:=PasteRng
Set PasteRng = PasteRng.Offset(1, 0) 'One row header

For Each Ccell In SearchRng
  Set CopyRng = FindRng3.Find(What:=Ccell, LookAt:=xlWhole)
  If Not CopyRng Is Nothing Then
    CopyRng.EntireRow.Copy Destination:=PasteRng
    Set PasteRng = PasteRng.Offset(1, 0)
  End If
Next

'Now add all the FindRng that are not found
Set PasteRng = PasteRng.Offset(1, 0) 'One row empty
PasteRng = "No Match: Found only in your input data"

Set PasteRng = PasteRng.Offset(1, 0) 'One row header

For Each Ccell In SearchRng
  Set CopyRng = FindRng2.Find(What:=Ccell, LookAt:=xlWhole)
  If CopyRng Is Nothing Then
    Set CopyRng = FindRng3.Find(What:=Ccell, LookAt:=xlWhole)
  End If
  If CopyRng Is Nothing Then
    Ccell.EntireRow.Copy Destination:=PasteRng
    Set PasteRng = PasteRng.Offset(1, 0)
  End If
Next

Sheets("Menu").Select
  MsgBox ("Server CI search complete. See 'Server Results' work sheet")
End Sub


I am new to Macros but not to Exce.

I am trying to write a macro that will go to cell d3 and automatically
copy that number and paste it into cell a12 and all the way do in column a to where data stops in column b. The data in column b will very every time the
macro is run.

I can do it manually, but when I run the macro it copies the same number
of cell down as used in the original macro. I don't want any data is the cell in column b is empty.

Copy, paste in a12-a54 first time
copy paste next time might be a12-a15 or a12-a101.

Can someone help? Couldn't find answer in any other thread.
Thanks

I have a list 4000+ lines long. There are rows empty sometimes every other row, sometimes every 2 rows etc.

In the cells that actually contain information, I used a formula to extract the last 2 words (which happen to be first and last name).

When I drag down the formula, where there are blank rows I get a #VALUE!. I figured I could do an if statement next to it that said, if the cell contains #VALUE!, leave it blank. If not, then I wanted it to carry over the cell that contained the name.

My guess is excel doesnt read that cell as being #VALUE! so it didn't work.

Is there a way to not have that error appear in those cells? Is there a way to do that if statement?

The formula I used was =MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,255)

Hello,

I have checked other threads before posting here, but even though I found lots of information, nothing was truly adapted to my situation.

Here is my problem:

I extract from a huge data sheet thousands rows of sales informations.
Using a pivot chart, I regroup those in a simple chart.

Then, from the pivot chart, I regroup my products by line and show the sales per day.
The chart looks pretty much like this:

01/06 02/06 03/06 04/06 05/06
orange 10 12 13 14 9
banana 2 0 6 4 8
kiwi 6 11 12 5 10

Ok, so far, all's good. Exporting this to a graph is piece of cake.

Now, I want to regroup those data in weekly and monthly sales. I have tried many methods, even merged cells (don't use merge cells!! I knowwwww), but nothing works.
Here is the concept:

A1 B1 C1 D1 E1 F1
01/06 02/06 03/06 04/06 05/06 06/06
orange 10 12 13 14 9 0
banana 2 0 6 4 8 5
kiwi 6 11 12 5 10 8
week 1 week 2
orange 212 320
banana 32 60
kiwi 156 49
month 1
orange 652
banana 452
kiwi 365

I have tried to organize it in a logical way: the week goes under each sunday, and the month under each first day of the month... logical, right?
Then I put all this in a graph... Problem is: for weeks, cells A1 and B1 are empty (in fact only one cell is full every 7 cells, to be really logical). Result: my graph shows one bar, six gaps, one bar, six gaps, etc...
Same for months (of course).

So, I tried merged cells... but as expected, even though "A1:C1" becomes just one cell... the graph still see several: problem not solved.

I have also tried to create a separate table: my weeks are not aligned with the days, but whatever, it should work, right?
No, it doesn't. Here is the table:

A50 B50 C50 C50
week1 week2 week3 week4
orange 510 112 413 614
banana 22 50 26 504
kiwi 206 111 12 125

Alright... making a graph out of it: easy.
Problem: getting those data in this chart!

How do I do. I say:

A50 = sum(A1:G1) means the sum of all the sales for this week.
Ok... then I do the same for B50:
B50 = sum(H1:O1) ok, still good.

Now, I drag my formula to have the same system everywhere... right. But it doesn't work.
Excel will copy this:

C50 = sum(I1:P1)
D50 = sum(J1:Q1)
etc...
where it should be:
C50 = sum(P1:V1)
D50 = sum(...)

Therefore, I do not get proper results, and, indeed, not proper graph.

If anyone had a solution, I would be very grateful.
If anything isn't clear, please let me know.

Thank you very much.

Edit: sorry my tables aren't clear, but I can't find out how to draw nice table on the website.
edit-edit: I added a picture in attachment for the table

Hi all.

I hope someone can help me out with the problem described here.

I've discovered that the complex math in Excel is badly implemented, since in english and danish, the different names means that the complex math stops working. For example we have:

English: IMSUB
Danish: IMAGSUB

and

English: IMPRODUCT
Danish: IMAGPRODUKT

If I create the spreadsheet in danish, it doesn't work in english version of Excel.

I thought I'd make a macro, which could search/replace these text strings. I'd place a
button and the user could choose language him-/herself. Just started looking into macros two days ago.

First I did a macro recording, which didn't continue to work. Simply because when I did the search/replace, the macro "forgets" to choose the entire workbook. It only searches the active sheet.

I continued by putting the words I want to search/replace into some cells. They are placed in Sheet "DK" in cell range B104-C104 as you can see below.

I coded the following:

Sub ToEnglish()
'
' ToEnglish Makro
' Makro indspillet 13-05-2010 af Claus Futtrup
'

'
    SaveActSheet = ActiveSheet.Name
    DK1 = Sheets("DK").Range("C103").Value
    DK2 = Sheets("DK").Range("C104").Value
    EN1 = Sheets("DK").Range("B103").Value
    EN2 = Sheets("DK").Range("B104").Value
    Sheets("5.47").Select
    Cells.Replace What:=DK1, Replacement:=EN1, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=DK2, Replacement:=EN2, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets(SaveActSheet).Select
End Sub
Sub ToDanish()
'
' ToDanish Makro
' Makro indspillet 13-05-2010 af Claus Futtrup
'

'
    SaveActSheet = ActiveSheet.Name
    DK1 = Sheets("DK").Range("C103").Value
    DK2 = Sheets("DK").Range("C104").Value
    EN1 = Sheets("DK").Range("B103").Value
    EN2 = Sheets("DK").Range("B104").Value
    Sheets("5.47").Select
    Cells.Replace What:=EN1, Replacement:=DK1, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=EN2, Replacement:=DK2, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets(SaveActSheet).Select
End Sub
Unfortunately the macro doesn't find anything to replace ... I am at a loss here. Running "BREAK" and "WATCH" the expressions DK1 and EN1 (stepping through the macro with F5) I can see that they stay empty ...

Please help me accomplish what I'd like to do.

Best regards,
Claus

Hello,
I have two problems to solve.

First to find last used row in sheet. I was trying looping through column with IsEmpty, but sometimes even if cell is empty and was not touched the result is negative and last row is not really the last one.

Second. How to print quoation marks in message box?
example: Msgbox(" he said "" + sentence + """)

Can anyone help me please?

I am fairly new to writing code and am using a Do Loop combined with an If
to find out if there is an empty string or a formula in a cell and if there
is to clear the cell completely until the cell it reaches has nothing in it.
However, at first whilst looping through the second condition was ignored
(formula) and now I get an error message saying I have a Do without a
Loop!!!???? I'm going bananas. The code I have written looks like below.
If anyone can help I would be eternally grateful. Many thanks.

Do Until ActiveCell.Value <> Empty

If ActiveCell.HasFormula = True Then
ActiveCell.Clear
ActiveCell.Offset(0, 1).Select
ElseIf ActiveCell.Value = " " Then
ActiveCell.Clear
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select

Loop

Hi folks,

I've been struggling to find a solution to one of the last hurdles of my application. I've lost a lot of time guessing at code to accomplish what I think needs to be done to allow me to proceed.

First, the relevant component of the userform. On my userform ("edit_wo"), I provide the user a listbox of data gathered by a second worksheet ("CONTROL_1"). The rows of this source data are referred to as individual records, each uniquely identified by a record number in column A. The listbox displays select columns from the source database.

First ... consider these two userform modules:

Private Sub AddMultipleColumn()
Dim rngCell As Range
Dim wks As Worksheet

Set wks = ThisWorkbook.Worksheets("CONTROL_1")
'Add multiple Columns to a listbox
ListBox1.Clear                          'Make sure the Listbox is empty
ListBox1.ColumnCount = 7                'Set the column Amount
wks.Unprotect
For Each rngCell In wks.Range("A1").CurrentRegion.Columns(1).Cells
' ignore header row
If rngCell.Row > 1 Then
'Fill the Listbox
With ListBox1
    .AddItem wks.Cells(rngCell.Row, "A").value
  '.AddItem rngCell.Value         'Additem creates a new row
   .List(.ListCount - 1, 1) = wks.Cells(rngCell.Row, "F").value
   .List(.ListCount - 1, 2) = wks.Cells(rngCell.Row, "H").value
   .List(.ListCount - 1, 3) = wks.Cells(rngCell.Row, "K").value
   .List(.ListCount - 1, 4) = wks.Cells(rngCell.Row, "L").value
   .List(.ListCount - 1, 5) = wks.Cells(rngCell.Row, "N").Text
   .List(.ListCount - 1, 6) = wks.Cells(rngCell.Row, "O").Text
End With
End If
Next rngCell

End Sub
The code above is what I use to populate the 7 column listbox. Since the number of rows in the source data ("CONTROL_1") is dynamic based on the dataset being used, the listbox data is compiled based on the number of rows in Column A (which is the unique record number column). This populates the listbox quite nicely with all the records in the source worksheet.

Having the records now available in thelistbox, the user can access the records to make review each one and make any ammendments necessary. As reviews and changes are made, the user clicks a [APPROVE] button which process the changes into the database, and sets a flag field (column DZ) to a value of 1 ... indicating the record has been reviewed. A default of "" is used to indicate a record has not been reviewed.

When the user is confident that the all records have been reviewed, they click a [SUBMIT] button which does some error checking and further data processing before eventually saving the dataset. Part of the error checking must include ensuring all the records have been reviewed. I use the flag value in column DZ for that. I count the number of unreviewed records (DZ = ""), report that number, and proceed with compiling a list of those record numbers (column A) in another worksheet ("Reference"). The code below does this.

Private Sub submit2_Click()

' have all records been reviewed?
    Dim r As Range, filt As Range, j As Integer, k As Integer
    Dim dest As Range
    Dim nr As Long
    Dim no_records2 As Long
    Worksheets("Reference").Range("AH3:AH300").Clear
    Worksheets("CONTROL_1").Activate
    no_records2 = Application.WorksheetFunction.Count(Columns("A"))
    nr = WorksheetFunction.CountIf(Range("DZ2:DZ" & no_records2), "")
    MsgBox "Unreviewed records: " & nr
    Set r = Range("DZ1").CurrentRegion
    r.AutoFilter field:=130, Criteria1:=""
    ' opt2
    '     MsgBox "There are records that have not been reviewed. Would you like to review these before proceeding with
reconciliation?"
    '     Call AddMultipleColumn
    Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    j = filt.Areas.Count
    For k = 1 To j
        With filt.Areas(k)
            .Columns("A:A").Copy
            With Worksheets("Reference")
                .Cells(Rows.Count, "AH").End(xlUp).Offset(1, 0).PasteSpecial
            End With
        End With
    Next k
    ActiveSheet.AutoFilterMode = False
    Application.CutCopyMode = False
    'Opt 1
         'MsgBox "There are records that have not been reviewed. Would you like to review these before proceeding with
reconciliation?"
         'Call AddMultipleColumn
End Sub
What I am having great difficulty doing, is updating the listbox now, to only display those compiled records that remain without review (DZ=1).

I tried two options (Opt1, Opt2 in blue). Neither worked. In both cases the lstbox continued to display all rows regardless of the value in DZ. The likely culprit is this line in the "AddMultipleColumn" module:
Since I did not write these two modules, I assume this line ignores any filters and defaults to all values in column A. 

Please ... someone help me figure out a way to ammend this code to populate only those records in DZ that are "" (empty) rather than just all the records in A alone.
If this is too complex, perhaps someone can advise of a resource for quick paid support.

Jenn

was wondering if anyone can please help me with this task I would to create a userform that will search a workbook in excel with mulitiple sheet, it could be 5-endless worksheets The way i would like to do this is a new tab is created when a new client is add to our database and be able to seach curtain critiera such Hospital name, Contact name,services,phone numbers(if possible)and User name (if Possible) It sounds more complacted then it seems i am going to try to attach the file and code is there anyone whom can help i will be thankful

[Option Explicit]

'Module Level Variables
Dim rRange As Range
Dim strFind1 As String
Dim strFind2 As String
Dim strFind3 As String

Private Sub ComboBox1_Change()
'Pass chosen value to String variable strFind1
strFind1 = ComboBox1
'Enable ComboBox2 only if value is chosen
ComboBox2.Enabled = Not strFind1 = vbNullString
End Sub
Private Sub ComboBox2_Change()
'Pass chosen value to String variable strFind1
strFind2 = ComboBox2
'Enable ComboBox3 only if value is chosen
End Sub

Private Sub CommandButton1_Click()
'Procedure level variables
Dim lCount As Long
Dim lOccur As Long
Dim rCell As Range
Dim rCell2 As Range
Dim rCell3 As Range
Dim bFound As Boolean

'At least one value, from ComboBox1 must be chosen
If strFind1 & strFind2 & strFind3 = vbNullString Then
MsgBox "No items to find chosen", vbCritical
Exit Sub 'Go no further
ElseIf strFind1 = vbNullString Then
MsgBox "A value from " & Label1.Caption _
& " must be chosen", vbCritical
Exit Sub 'Go no further
End If

'Clear any old entries
On Error Resume Next
ListBox1.Clear
On Error GoTo 0

'If String variable are empty pass the wildcard character
If strFind2 = vbNullString Then strFind2 = "*"
If strFind3 = vbNullString Then strFind3 = "*"

'Set range variable to first cell in table.
Set rCell = rRange.Cells(1, 1)
'Pass the number of times strFind1 occurs
lOccur = WorksheetFunction.CountIf(rRange.Columns(1), strFind1)

'Loop only as many times as strFind1 occurs
For lCount = 1 To lOccur
'Set the range variable to the found cell. This is then also _
used to start the next Find from (After:=rCell)
Set rCell = rRange.Columns(1).Find(What:=strFind1, After:=rCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'Check each find to see if strFind2 and strFind3 occur _
on the same row.
If rCell(1, 2) Like strFind2 And rCell(1, 3) Like strFind3 Then
bFound = True 'Used to not show message box for no value found.
'Add the address of the found cell and the cell on the _
same row but 2 columns to the right.
ListBox1.AddItem rCell.Address & ":" & rCell(1, 3).Address
End If
Next lCount

If bFound = False Then 'No match
MsgBox "Sorry, no matches", vbOKOnly
End If
End Sub

Private Sub CommandButton2_Click()
'Close UserForm
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Check for range addresses
If ListBox1.ListCount = 0 Then Exit Sub
'GoTo doubled clicked address
Application.Goto Range(ListBox1.Text), True
End Sub

Private Sub UserForm_Initialize()
'Procedure level module
Dim lRows As Long

'Set Module level range variable to CurrentRegion _
of the Selection
Set rRange = Selection.CurrentRegion
If rRange.Rows.Count < 2 Then ' Only 1 row
MsgBox "Please select any cell in your table first", vbCritical
Unload Me 'Close Userform
Exit Sub
Else

With rRange
'Set Label Captions to the Table headings
Label1.Caption = .Cells(1, 1)
Label2.Caption = .Cells(1, 2)
Label3.Caption = .Cells(1, 3)

'Set RowSource of ComboBoxes to the appropriate columns _
inside the table
strSheet = ListBox1.List(ListBox1.ListIndex, 1)
strAddress = ListBox1.List(ListBox1.ListIndex, 2)

End With
End If
End Sub

Private Sub UserForm_Terminate()
'Destroy Module level variables
Set rRange = Nothing
strFind1 = vbNullString
strFind2 = vbNullString
End Sub
Attached Files

First of all: Thanks to all of you, who are way smarter than I, who've helped me accomplish years of MS Office reporting and tool building, without the need for classes, books, or yet another username and password!

It seems I've finally come up with a need that exceeds what I can piece together from recorded macros, and the solultions you've provided for others.

I have a rather large file that I use to unify the data from several different html based tools my company uses (that lack the tools I require), and several excel based reports that come from outside our company (that lack ALL the referred data I need to make them useful). It hasn't ever been a terribly easy tool to use, since I designed it for myself, and do not require the automation or protection that a less avid excel user would. It's recently become a priority item to incorporate in all our offices areas across the country, so automation is now a must. I've run into a wall on the last item.

At the front of the sheets I have a simple pivot table that aids in the quick and easy filtering of thousands of records. The point of this table is to be able to easily draw a route around a local area based on the most relevant jobsites. I've used a simple formula in the past to create the URL text to plot multiple adresses in google maps, so as to prevent back tracking, and create tighter routes. It's always there, I just paste the filtered data in, copy the resulting cell into a browser window and voila! It's starting to look like even that is too many steps for some. Problem is that after about 4-5 addresses I exceed the 255 character limit of =hyperlink, and a route can regularly include 20-30 addresses.

I've attached a file (STI Sample Data) with two sheets
(Sample Filtered Table) - A sample of the filtered table my pivot table produces which includes the addresses, separated into 4 columns.
(Sample URL) - Details of how the URL is built, with a sample of the single address hyperlink, and details of how additional addresses are added.

What I would like to do is create an empty sheet, into which one could copy the filtered table, and initiate a macro that would open their browser and input the URL based on address info it contains. OR EVEN BETTER, find a way to make that button automatically appear in the resulting new sheet created by double clicking pivot table values!

Sorry for the long post, it's a complicated request, and I figure solving a problem with too much data is easier than not enough. Thanks in advance, I know this isn't just a few lines of code.


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