Re: Percentage true value
You have to 'adjust' the number, especially if it is a percentage
0.1052 in A1 - formatted as Percentage with 0 decimal places -> displays 11%
Formula in B1 =ROUND(A1 * 100, 0)
Re: Percentage true value
You have to 'adjust' the number, especially if it is a percentage
0.1052 in A1 - formatted as Percentage with 0 decimal places -> displays 11%
Formula in B1 =ROUND(A1 * 100, 0)
Re: Vba code is taking too much time to do job
QuoteThere is no reason
Rather than derail the topic: http://www.ozgrid.com/forum/showthre…2295#post782295
Quote from royukThere is no reason or excuse for being sarcastic when replying to posts.
http://www.ozgrid.com/forum/showthre…2267#post782267
I wasn't being sarcastic, just pointing out a simple fact.
Anybody, even with the most basic knowledge of VBA would understand the reason for the error. Anyone without that level of understanding should be able to to resolve the issue with a simple search or even a dip into the Help files (remember them?) - the added bonus is that they will learn something and will then be able to catch similar issues themselves in future.
If they're not competent to do even that then they should seriously question what they are doing in their position.
So the reply is posted putting the onus on others to save this person the trouble of spending a couple of minutes trying to resolve it themselves.
The latest post in that thread is yet another example:
Quotewill this process slow down the copy paste value?
The code exists, the poster has a copy of the workbook. All he has to do is make some adjustments to the code to test his query himself. I am assuming he can make those changes.
However, that's not time efficient for him, best lay it off on someone else while he goes and finds the next simple issue to post on a forum (and he does post on multiple forums, more than once he has 2 or 3 others on each forum, happily chasing around trying to solve his problems for him.
Another example of time saving on his part
Quotenot working
That is the entire post. My comments on that would not have been as polite as
Quote from bryceNot working? Well good luck to you friend. How about something like: Thanks for trying, when I ran it this is what I saw and compared to my original here is the performance
It's not that there's a language barrier or inexperience of forums - the person who started that thread has over 900 posts on ExcelForum so he does know what he is doing (as far as posting is concerned).
Answering questions on forums truly is a mugs game these days. Only 28 posts, and out. Goodbye.
Re: OzGrid Excruciatingly Slow!
There is no configuration for TRACERT and it does not change anything on your system. It is an application that simply reports the time taken for each server hop in the path to the site.
[ATTACH=CONFIG]70850[/ATTACH]
It's not going to cure issues but might help to show where problems are occurring.
Re: Macro for executing commands from cmd prompt
Posted on another forum as well. No link.
Re: Edit each cells but only unique one using vba
The original question also posted on ExcelForum, no links in either forum.
Can't see the supplementary question yet. Give it time
Re: OzGrid Excruciatingly Slow!
Have you tried TRACERT from a command prompt (assuming Windows)?
TRACERT can be used to determine if intermediate servers between you & Ozgrid have configuration issues. My guess is you will find at least one non-responsive server as other users (on a different 'path' to Ozgrid) do not have the issue.
Re: Search worksheet return hyperlink
Rough code copied from somewhere ages ago, but it works as long as you add a new worksheet called Summary
Public Sub FindAll()
Dim WB As Workbook
Dim WS As Worksheet
Dim Cell As Range
Dim Prompt As String
Dim Title As String
Dim FindCell() As String
Dim FindSheet() As String
Dim FindWorkBook() As String
Dim FindPath() As String
Dim FindText() As String
Dim Counter As Long
Dim FirstAddress As String
Dim Path As String
Dim Search As String
Prompt = "What do you want to search for in the worbook: " & _
vbNewLine & vbNewLine & Path
Title = "Search Criteria Input"
Search = InputBox(Prompt, Title, "Enter search term")
If Search = "" Then
GoTo Cancelled
End If
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo Cancelled
Set WB = ActiveWorkbook
For Each WS In WB.Worksheets
If WS.Name <> "Summary" Then
'Search whole sheet
With WB.Sheets(WS.Name).Cells
'***********************************
'Alternative to search single column
'With WB.Sheets(WS.Name).Range("B:B")
'***********************************
Set Cell = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False, SearchOrder:=xlByColumns)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Counter = Counter + 1
ReDim Preserve FindCell(1 To Counter)
ReDim Preserve FindSheet(1 To Counter)
ReDim Preserve FindWorkBook(1 To Counter)
ReDim Preserve FindPath(1 To Counter)
ReDim Preserve FindText(1 To Counter)
FindCell(Counter) = Cell.Address(False, False)
FindText(Counter) = Cell.Text
FindSheet(Counter) = WS.Name
FindWorkBook(Counter) = WB.Name
FindPath(Counter) = WB.FullName
Set Cell = .FindNext(Cell)
Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
End If
End With
End If
Next
'If no result found, reset properties and exit sub
If Counter = 0 Then
MsgBox Search & " was not found.", vbInformation, "Zero Results For Search"
'Clear old results if required
'Range("A3", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)).ClearContents
'**********************************
GoTo Cancelled
End If
'Add SearchWord sheet if not present
Sheets("Summary").Select
'Clear old data and then format results page as required
Range("A3", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)).ClearContents
Range("A1:B1").Interior.ColorIndex = 6
Range("A1").Value = "Occurences of: "
Range("B1").Value = Search
Range("A1:D2").Font.Bold = True
Range("A2").Value = "Location"
Range("B2").Value = "Cell Text"
Range("A1:B1").HorizontalAlignment = xlLeft
Range("A2:B2").HorizontalAlignment = xlCenter
With Columns("A:A")
.ColumnWidth = 14
.VerticalAlignment = xlTop
End With
With Columns("B:B")
.ColumnWidth = 50
.VerticalAlignment = xlCenter
.WrapText = True
End With
'Add hyperlinks and results to spreadsheet
For Counter = 1 To UBound(FindCell)
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & Counter + 2), _
Address:="", SubAddress:="'" & FindSheet(Counter) & "'" & "!" & FindCell(Counter), _
TextToDisplay:=FindSheet(Counter) & " - " & FindCell(Counter)
Range("B" & Counter + 2).Value = FindText(Counter)
'Add text from offset columns; probably not
'appropriate with whole sheet search
Range("C" & Counter + 2).Value = _
Sheets(FindSheet(Counter)).Range(FindCell(Counter)).Offset(0, 1)
Range("D" & Counter + 2).Value = _
Sheets(FindSheet(Counter)).Range(FindCell(Counter)).Offset(0, 2)
'*********************************************
Next Counter
'Find search term on results page and colour text
ColourText
Cancelled:
Set WB = Nothing
Set WS = Nothing
Set Cell = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Display More
Re: Reverse order of text characters in a string
Can't you use the same software that "messes up the strings" to 'unmess' them up?
Re: Search worksheet return hyperlink
Do you mean you want to search for the name in the Hyperlink address or as text in the cell?
Re: change from date format to different date format
What you do is determined by the format of the field in the recordset. If defined as a date then VBA should be able to recognise it, if a string ('20161124') then you can use the cDate function to recreate a valid date.
CellAddress.Value = cDate(Right([FieldName], 2) & "/" & Mid([FieldName], 5, 2) & "/" Left([FieldName], 4))
'CellAddress' and '[FieldName]' should be replaced by the actual; cell address and DB field names...
(Assuming your local date format is dd/mm/yyyy)
Re: Select case works sometimes and not others
n is always 0 in sub CaseSelect.
This is because you derclare and initialise it in sub Roster making it local to that procedure. You need to pass the value to CaseSelect
and then change the declaration for CaseSelect
This type of error can be avoided by using the Option Explicit statement at the top of each module
Re: Copy top 3 values from data to another column
Answered and acknowledged on another forum.
Re: Merge xls files in one xlsx file
Posted on at least 2 other forums.
Re: 1 Checkbox to hide then unhide multiple sheets.
It's an issue that has come up before and seems to be one of those 'things' in Excel - one workaround:
Re: Do While Loop
My mistake.
Re: Replacing first 6 characters of a sequence in VBA
Posted on Excelforum too
Re: Formulating word formulas for adjacent excel sheets
Answered on ExcelForum