try this formula in cell G19 and fill it down make sure you press ctrl+shift+enter bcoz its array forumla and not just enter
INDEX(A:A,SMALL(IF($C$19:$C$28=F19,ROW($C$19:$C$28),""),ROW(A1)))
Note Press Ctrl+shift+enter
1 change .ProgressBar1.Max = 10000
change its max value with total no of files which you want to import
2 For i = 1 To 10000
change "10000" with total no of files
3 .ProgressBar1.Value = i
Add your code of import before this line
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...
@NARAYANK991 ,shrivallabha ---- Thanks I missed that part :(
@ianb - Try this updated one
Sub goto_first_cell_in_each_worksheet()
Dim wk As Worksheet
For Each wk In ThisWorkbook.Worksheets
If wk.Visible = xlSheetVisible Then
wk.Select
If ActiveWindow.SplitRow = 0 And ActiveWindow.SplitColumn...
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 = "No Comment Found"
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 "No freeze Pane Found"
Exit Sub
Else
MsgBox Cells(ActiveWindow.SplitRow + 1...
Some other resource for Google Map and Excel Integration
http://ramblings.mcpher.com/Home/excelquirks/getmaps
http://oco-carbon.com/2012/03/06/google-excel-distance-function/
Free Add-in
http://www.mapcite.com/excel-addin-free.aspx
@SirJB7 Yes you will see lot ppl using this method most of the steps is already covered on Google Map API documentation site like how to create a web URL and what will be the output in XML format, etc.
All you will find different is the tricks used to extract the data from XML result.w
btw...
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...