Search results

have u tried this one =INDEX(A:A,SMALL(IF($C$19:$C$28=F19,ROW($C$19:$C$28),""),COUNTIF($F$19:F19,F19))) ctrl + shift + enter

try this one =INDEX(A:A,SMALL(IF($C$19:$C$28=F19,ROW($C$19:$C$28),""),COUNTIF($F$19:F19,F19))) ctrl+ shift + enter

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

@ Ian Try these Sample Macro https://www.box.com/s/0a6b971ebda2d944017f Working Example https://www.box.com/s/75768cb330466a7b2481

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...

@ sgmpatnaik - Thanks

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...

@shrivallabha -Thanks

Exit Sub - It was by mistake i i tried to remove it but I don't know the way to edit

@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) -...

@Ian -Its working fine at my place. I am using Excel 2007 Version. Try wk.Activate or wk.select

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...

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...

Thank you All @deb visit this one too http://excelvbaprogramming.wordpress.com/

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...