Search results

Hi DashboardNovice Try the following. =TRIM(RIGHT(SUBSTITUTE(B4,":",REPT(" ",255)),255)) Take care Smallman

Hi @rrocker1405 It is a good idea to not select the Data in VBA as your coding should run more quickly. I tested your code and it worked on my machine. What does your data look like and do you really have 65K rows of data. Maybe trapping a dynamic range in VBA. Sub testo() Dim rng As...

Hi Sealion1 In E2 put the following formula =IF(C2="","",WORKDAY(C2,D2)+2) In G2 put this =IF(C2="","",WORKDAY(E2,F2)+2) That should cover off your blank issue. Take care Smallman

Tim Would have been a lot easer rather than pointing us to a macro RemoveRowsByMatch to just say what you are trying to do? For example, I want to make a list of the matching items between sheet 1 and sheet2 on sheet 3. You would have an answer already. I am not even sure the Want sheet...

Hi Blazy I changed Row 34 from text to numbers. This helped with formula. As you have a table dedicated to each month's rank I used an index and match formula. See attached file. Take care Smallman

Can we see this file please? Most people would take the time to recreate the problem on a smaller scale if privacy is an issue. Upload something that fails so we can advise. It may help you see the problem if you try and recreate it anyways. Take care Smallman

@asparagus A small suggestion - insert a column in Col A - in A5 put your date (29 Nov) in A7 place this formula =MATCH($A$5,C7:G7,0) Drag down. Now all you need is a filter - copy your data. Or if you are dead keen on VB for this simple task then this. Sub Isolate()...

Dave When you say adjacent what do you mean - to the left or to the right of the cell you are testing. I put this together so it hide the cell adjacent to the right. Option Explicit Sub HidePlus() Dim i As Integer For i = 1 To Range("IV97").End(xlToLeft).Column Step 2...

Hi @ONKAR BHOSALE My example links it in a list. If you change any of the names in the list the picture will automatically update. You will need to be more specific if you want a more detailed response. Take care Smallman

Hi Ajaar Give the following a try remembering to change the path. Option Explicit Sub SavetoWB() 'Excel VBA to export data Const sPath = "C:\Test\" Dim ar As Variant Dim i As Integer Dim owb As Workbook Application.ScreenUpdating = False Range("F1", Range("F" &...

Hi Shaherukh The trick the this excercise is to name the pictures in the file exactly the same as the names in your drop down. Here is an example Insert Pictures You won't be able to see the pictures when you open the file because you don't have access to this drive. fName =...

You are correct. I forgot to put the last part of the formula on. =SUMPRODUCT((Data!$A$2:$A$10=$A2)*(Data!$B$1:$Q$1>=$B$1)*(Data!$B$1:$Q$1<$C$1)*(Data!$B$2:$Q$10)) That should see you right. Smallman

Hi lucianogh Welcome to the chandoo forum. :) The first thing you need to do is to get rid of the text headers. These make it very difficult to sum, quarters, 6 months and years. Once you add dates as your headings summing between the dates becomes reasonably straight forward. Here is...

Hi Inddon I might suggest an alternative. Take the button out and just have a worksheet change event. Some thing like the following. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Not Intersect(Target, [D3]) Is Nothing Then Set rng = Range("D5", Range("D"...

Hi Govi Would you like to share your solution so others may learn from your endevours? Thanks Smallman

Hi I think you will need to do this in VBA as any date reference in formula will be a moving feast. I think you will need to test two criteria, has the student submitted their essay and is there already a date in Column G. If you do not test two criteria then the dates will be overwritten...

Hi If you put a count at the bottom of your table the code would be easier to apply. Option Explicit Sub HideIt() Dim rng As Range For Each rng In Range("B12:F12") If rng = 4 Then rng.EntireColumn.Hidden = Not (rng.EntireColumn.Hidden) Next End Sub Sub UnHideIt()...

Hi @asparagus This question relates to your last post. You want data to be filtered between two dates using the autofilter feature. I have written a post on this topic which you can see here; Autofilter between dates This should be able to be manipulated by yourself. Takec care Smallman

Hi MSC Bobs Very happy welcome to the chandoo forum. Sumproduct is one of my favourite formulas in Excel, even though Sumifs has moved in I still prefer it. The Stocks table has text in it were there should be numbers. Sumproduct is a very tempremental beast. When you remove the text...

You are most welcome @asparagus Good luck with the rest of your project. Smallman

OK So my excel file is a perfect test environment. I just ran the code on the first file and it ran like a dream. Might help if you quote the correct ranges. I changed the range and this is the code I used. Option Explicit Sub MoveDates1() Dim StDate As Long: StDate = Now + 2...

Check your dates. Probably a problem with your dates. Here is the file I ran. Just ran it again and it ran very smoothly. Make sure you check your file path to save as your method looks a bit off. You need to name the file and the path. Take care Smallman

Hi asparagus Give the following a try. Sub MoveDates() Dim StDate As Long: StDate = Now + 2 Range("B6:B2000").AutoFilter 1, ">=" & StDate Range("A6:EW2000").Copy Workbooks.Add [A4].PasteSpecial xlPasteValues ActiveWorkbook.SaveAs "D:\Users\muhammad.galih\Dekstop\Reminder...

Hi @tomas If you are trying to hard code the cells in the active column best not to copy and paste values, best to tell Excel that you want the cells to equal their current value. A1.value = A1.value Here is an example using your problem. Sub Quicker() Dim i As Long Dim rng As Range...

Hi Excel Peppy The best way to do this sort of thing IMO is with conditional fomatting. Here is an example of the CF formatting =if(A2=1,1,0) ....... Red =if(A2=3,1,0) ....... Blue =if(A2=5,1,0) ....... Green File attached to show workings. Take care Smallman