Search results

If you want to extract hyperlink address from a cell. Try this udf- Function hyperlink_extcell(cell As Range) If cell.Hyperlinks.Count > 0 Then hyperlink_extcell = cell.Hyperlinks(1).Address Else hyperlink_extcell = "Hyperlink Not Found" End If End Function

If you to extract/pick a word by its postion from a cell . For example 1) Input: Procter & Gamble Output(Pick 3rd word): Gamble 2) Input: Procter,&,Gamble Output(Pick 1st word): Procter Try this udf : Function pick_word(str1 As String, spl As String, positon As Integer) Dim arr1 arr1 =...

If you want to concatenate the first letter of each word in a cell .For example - Input Output Johnson & Johnson J & J Procter & Gamble P & G Boston Consulting Group B C G Try this udf Function con_1stletter(str1 As String) Dim arr1, i As...

If you want to find the Latitude and Longitude of any address using MapQuest . Try this UDF- To know more about MapQuest API visit - http://open.mapquestapi.com/geocoding/ Function lat_lon_mapquest(a_t As String, c_t As String, s_t As String, co_t As String, z_t As String) Dim sURL As String...

If you want to concatenate non blank cells in a range. Function concatenate_nonblanks(irng As Range, spl As String) Dim cell As Range Dim rsl As String For Each cell In irng If cell <> vbNullString Then rsl = rsl & spl & cell End If Next concatenate_nonblanks = Right(rsl, Len(rsl) -...

If you want to check if cell is BOLD or not . Try this UDF- Function Is_Bold(xa As Range) Is_Bold = xa.Font.Bold End Function It will return True if Cell is bold and False if not

If you want to know the RGB Value of a Fill color . Try this UDF Function rgb_color(cl As Range) As String Dim rgbc As Long, rc As Long, gc As Long, bc As Long If cl.Cells.Count = 1 Then rc = cl.Interior.Color Mod 256 rgbc = Int(cl.Interior.Color / 256) gc = rgbc Mod 256 bc = Int(rgbc / 256)...

If you want to extract the comment from a cell. Try this UDF- Function extract_comment(cmt_rng As Range) As String If Not cmt_rng.Comment Is Nothing Then extract_comment = cmt_rng.Comment.Text Else extract_comment = &#34;No Comment Found&#34; End If End Function

If you want to select the first cell after freeze pane on each worksheet and save it. So that when user opens the workbook he/she do not have to press CTRL+ Home in each worksheet to go to first cell. Here is the code - Sub goto_first_cell_in_each_worksheet() Dim wk As Worksheet For Each wk In...

If you want to know the first cell after the freeze pane . Try this code- Sub find_first_cell_after_freeze_pane() If ActiveWindow.SplitRow = 0 And ActiveWindow.SplitColumn = 0 Then MsgBox &#34;No freeze Pane Found&#34; Exit Sub Else MsgBox Cells(ActiveWindow.SplitRow + 1...

If you want to know the distance between two cities by passing the address of destination and Origin place using "Google Distance Matrix API" in VBA. Download Working File https://www.box.com/s/3ai8xgasra8kpsgt40lq Copy the below udf and paste it any new module of your workbook Public...

If you want to find the distance and time taken between tow places by passing the Latitude and Longitude of Origin and Destination Via VBA Excel and Google Map. Here is the UDF Public Function get_dis_and_time(lat_1 As String, lon_1 As String, lat_2 As String, lon_2 As String) ' Read more...

If you want to save a range as HTML table on HTML webpage. Here is the code Download Working File https://www.box.com/s/7h58p020dasqcp01ayov Sub send_range_as_html_table() ‘ used to insert a line ( press enter) ‘ create a table using html ‘ check the link below to know more about html...

If you want to find Latitude and Longitude of any address . Download Working File https://www.box.com/s/h733p8zufxkkum8nsi2d Here is the UDF Function lat_lon(a_t As String, c_t As String, s_t As String, co_t As String, z_t As String) Dim sURL As String Dim BodyTxt As String Dim apan As...

If you want to get the names of all the folders in a directory/folder ( Excluding Sub folders).Try below code- Sub folder_names_in_a_directory_excluding_subfolder() Application.ScreenUpdating = False Dim fldpath Dim fso As Object, j As Long, folder, SubFolders, SubFolder With...

If you want to get the names of all the folders stored/created in a directory/folder ( Including Sub folders).Try below code- Sub folder_names_including_subfolder() Application.ScreenUpdating = False Dim fldpath Dim fso As Object, j As Long, folder1 As Object With...

If you want to add a new menu on mouse right click showing you the list of all open workbooks and worksheets in each of these workbooks. So that you can navigate easily. Add this to workbook Module Private Sub Workbook_Open() &#39;http://www.excelvbamacros.com/2012/04/blog-post.html On Error...