Search results

No change it to .clearcontents and you will need to do it like this cells.clearcontents or specify a range. Take care Smallman

Hi Jack Just a suggestion but why not use the Values Found sheet as a template. Instead of deleting the whole sheet just clear the contents and put the new data in that sheet. That would seem an easy work around. Take care Smallman

Your blank rows were the item I had not considered. Shame you are not able to make your data a best practice tabular layout. It makes a lot more sense to start with strong design and work for me there. I have altered the coding by putting A Yellow and Blue line at the bottom of the test file...

This bit: You said you wanted it static not user driven. The opposite of what you just said.

Hi fantabulator Do a test on the pdf code below. It should work but if now provide a file please. So we can see what is in j3 j28 and K28. Option Explicit Sub CreatePDF() Dim str As String Dim fName as String str = "C:\My Documents\" fName = [j3] & [J28] & [K28]...

Oh You have several tables not one??? What a crucial bit of information to leave out of your example. Your 6 line gap between each table contain the same characteristics as your deleted lines. How are you supposed to distinguish the difference between the lines which are separating your...

Merry Christmas everyone. Hope you all have a lovely day!!! Smallman

Hi Tim A non looping alternative. Sub Goski() Sheets(Array("DDD", "AAA", "ZZZ")).Copy End Sub Copies to a fresh workbook in the desired order. Take care Smallman

Hi Cruiser Can you please upload that file :) Take care Smallman

Hi Realhajiev Your file did not have any duplicates or any dates later than the date listed in R2? I have fixed your file so there is an example to check the coding with. The following will work. I highlighted the lines I have changed form the above. I added an error trap in case your...

Hi realhajiev I will have a look at this later tonight as I am heading out to play tennis tonight. I noticed your headings were split over two lines. My solution will remove line 4. As a general rule headers should always be on one row. It makes managing the data easier and creating quick...

Hi madocar Here is the same sort of example using a slightly different formula. Take care Smallman

Hi In your little loop VA will never equal "Tea" because you have assigned it to Category(i) so your loop will never achieve the result you wish. Sub Test() Dim i As Integer Dim Va As String Dim Vb As String Dim teas As String For i = 1 To 20 Va = "Category" & i Vb =...

Aria Sometimes the best solution is the simpliest one and formulas in cells tend to work faster than VBA loops and the time it takes to write and maintain a procedure. In my opinion putting two formulas in 2 columns will largely beat creating a vba procedure to replicate this formula. Take...

Ok So you want to understand how to manipulate this coding. Start here. With Sheet1.[C8].Resize(UBound(ar, 1), UBound(ar, 2) - 1) Notice the -1 at the end. The result will give you Column 1 to 3 not Column 1 to 4. If you want other columns excluded as in if you have not presented a like...

Hi Would you like to double check the data in A2. You are saying it is a number but does it just look like a number and is maskerading as text. The code seems to work OK. So just need to see your file now. Take care Smallman

Hi realhajiev The file and coding I provided was just an example. Such a small amount of it needs to change to get what you require. It was designed to handle any amount of data not be static on the dataset you provided. I triplicated your list and ran the code over your original file...

@rajkenya1 I made the assumption that the interest was paid at the end of the quarter. Calculating the interest earned then is a simple matter of arithmetic. Just take principal + 4 * (In - Out) and you have your answer. Take care Smallman

Hi I was interested in this because it took me back to school days. This is the old compound interest stuff. I did what Narayan did and worked it out long hand =C4*(1+(C7)/4)^$C$8+(C5-C6)*(1+(C7)/$C$8)^3+(C5-C6)*(1+(C7)/$C$8)^2+(C5-C6)*(1+(C7)/$C$8)^1+(C5-C6) Then I used my long hand to...

Hi Like to help but I can't see a file with these tables. Can you upload one. Ta Smallman

Hi I was looking at this last night and it got too late. I saw Narayan's response last night which he would not have been able to put together in NZ as it would have been too late even for him :) I looked at the problem a little more simply. If there was a duplicate and in the second...

Hi Aria Welcome to the Chandoo forum. I would stay away from VBA and use a fuzzy match with a list which you maintain. As new items come on you could add them to the list. I think 80-90% of your items you would trap in the first round if not all of them. This currently traps 100% see file...

Asparagus If you have a look at your coding there is no line called Worksheets("Partlist") You are confusing yourself. There is a worksheet called Thailand and I took the selection statement from that because in VBA using select statements adds more lines than necessary and selecting...

Hi Asparagus Nothing wrong with the code. You are asking the wrong question with the information you have supplied. You have provided everything but the email coding. Why don't you just test that it gets to the email part with a msgbox msgbox "We are up to here" Instead of the email...

Hi I put your code into a regular module for testing. It is easier to test code when it is in a regular module as you can put break points in the code and this is not available in the worksheet open. Anyways I have run the code and it runs fine. It is curious that you used the faster...