vba

  1. C

    Combobox value to display adjacent cells in textboxes

    I have the following code from a previous project i worked on and tried to modify it to find the value of a cell in column A and if the value matches, then display the two adjacent cell values in the two textboxes i have on the user form: Dim i As Integer Dim arrIn As Variant Dim arrOut() Dim j...
  2. D

    Convert Entire Sheet to a Named Table for a Pivot Table

    Hi guys, I have looked online to try and find some solutions but I keep getting an error. Perhaps I am messing up with the syntax. I want the vba to go to the worksheet titled "Index", and then to convert all of the data on that sheet into a table called "Source" (similar to going to the...
  3. W

    VBA Code to get distinct count with muliple criteria

    Can some one help me with a VBA code that gives distinct count of data in in "I" Column of sheet1 (dynamic) after filtering "Temp" and "Tem" in A column and "MX" in E column. I have a code as below which gives distinct count from "I" column but not able to add the filters. Would be great if get...
  4. S

    Help with Find command - Match names on multiple tabs

    Hi there, new member here - self taught excel vba so still a beginner and hoping someone may be able to help point me in the right direction or be able to offer code which could work with what I need. I am working on a spreadsheet that has to be completed quarterly so am looking to automate a...
  5. S

    Ambiguous name detected unable to run code

    Private Const AuditSheets As String = "|Sheet3|" ' Must start and end with the "|" character Private Const AuditRange As String = "$C$2:$G$32" ' Change the tracked range as necessary Private Const LifeSpan As Double = 0.0013 ' 24 hour lifespan Private NextTime As Date Private Sub Workbook_Open()...
  6. E

    Index / match with several criteri

    Hello Guys, I m trying to do Index / match with several criteria but the formula is not working. {=INDEX(PointageStructure!$G$2:$G$1000,MATCH(1,(D2=PointageStructure!$B$2:$B$1000)*("Sub-task"=PointageStructure!$C$2:$C$1000)*("Analyste"=PointageStructure!$M$2:$M$1000),0))} Do you have any clue...
  7. A

    How to allocate amount based on Payment Start Date and End Date thru VBA

    Hi, I'm using a formula to allocate the amount evenly based on the frequency of payment, due to numerous projects it is quite difficult to change the reference for each project, how we can achieve the same through VBA. The results are in column G to J.
  8. E

    Substitute error

    Hello Guys, I m trying to get this forumla below working but i keep getting this error: Unable to get the Substitute property of the WorksheetFunction class Range("M" & cell.Row).Value = Right(cell.Value, Len(cell.Value) - Application.WorksheetFunction.Find("@"...
  9. S

    Occasional runtime 1004 error

    Hi Folks, I'm running the below code...and it works fine...some of the time. Other times I get a runtime error but I can't figure out how to resolve it; any tips? The bit where I get an error is; For Each shp In ActiveSheet.Shapes If Not Intersect(Range(shp.TopLeftCell...
  10. J

    VBA code to locate specific already filtered columns by name and paste into a new workbook

    Hi All, So to give some background, i have a large data set (60k plus rows and 90 plus columns) that i need to filter and create a new workbook using specific columns. The size of the file changes daily, and columns are taken out and added. So i had to keep updating the previous macro i had...
  11. S

    Paste Multiple Selections

    Hi, I'd like to be able to paste two ranges from another workbook (the data source) to a final workbook using with two macro buttons (one in each) one will say copy and the other paste (in the final workbook). Copying seems to be okay as you can do something like the below or use a Union...
  12. D

    VBA Code but ignore sheet

    Hi all I have the following code which works well in part but Private Sub COPYTEAM() Application.Calculation = xlManual Application.ScreenUpdating = False Dim LastRow As Long Dim ws As Worksheet Sheets("Template").Visible = False Sheets("Team").Visible = True Call ClearTeam...
  13. K

    Sum Cell with Color from conditional formatting

    Hi I need your help. Anyone know how to sum cells/rows based on cell color from conditional formatting? I created a VBA code below but I wasn't able to get the sum probably because the colors is from conditional formatting. Basically I want to sum those cells with blue color in each row (S-AF)...
  14. A

    VBA getElementsByTagName fail to catch "td" tag elements

    I need some help from the smart guys here on the site. The code snippet below don't give any errors and it looks that it works fine, but the TD element collection stays empty and the innerText is empty too. That while the data are showing in a visual browser as You can see in the image below...
  15. W

    VBA - Moving row to another table

    Hey all! I have a tough time doing a table in Excel that my dad asked me to do :P. I'm totally new into VBA and I could use some help from you. The thing is that I have 2 identical tables in separate sheets. My goal is to move a row from table in sheet1(in progress) to the table in...
  16. A

    Noob here - please help with a simple problem

    I've started trying to do some simple stuff in Excel VBA and can't see to figure out the right code for what I want to do This is my code. It simply aims to copy a cell from one worksheet to another when the user clicks on it. I managed to restrict the clickable cells to c4:c429. This works...
  17. E

    Copy range without header and past it after particular row

    Hello guys, I'm stuck a little bit and need you help :) Right now, i m able to copy range content with header and past it in another worksheet and this is my code 'Workbooks("Projects.xls").Worksheets("Projects").Range("A:G").Copy...
  18. S

    Delete two lines above bookmark - keep bookmark VBA

    Hello, I hope you all are well. I want to write a macro to delete two lines above a bookmark in a word document. So far so good, but the bookmark itself should not be deleted. I have come up with the following code: Sub DeletetwoLinesAboveBookmark1() Dim r As Word.Range Set r =...
  19. D

    VBA Loop Though Column of Data, Select Shape in offset column

    Hi. I'm trying to figure out how to loop through column "I" If it finds a cell that is blank, skip the row, move to next row until it finds a cell containing a value Once it finds a cell with a value in column "I", select the picture in column "B" of the same row. Once the picture is selected...
  20. G

    Count consecutive negative numbers

    I am looking for a formula or vba, whichever is easier, where if the value in the row is negative, it will count until the next 0 or positive number, for the whole column. An example is below. I searched and couldn't find the exact thing I was looking for. Any help will be greatly appreciated...

Some videos you may like

This Week's Hot Topics

Top