.value

  1. Emanuele

    Transpose Data

    Hi guys, i would like to know how to add the transpose option to that part of code Thx! Set DestSh = ActiveWorkbook.Worksheets.Add Last = LastCol(DestSh) Set Copy1 = sh.Range("C10:AZ10") With Copy1 DestSh.Cells(1, Last...
  2. N

    Macro to add 1 to 4th then 5th cell for 200 numbers

    A different number is inputed in cell cd31 every day. Today's number in cd31 is 401 Then I want CD35 to be 402 Then I want CD40 to be 403 Then I want CD44 to be 404 Then I want CD49 to be 405 until last cell: CD926 = 600 Need a macro to increase by 1 every 4th then 5th then 4th then 5th cell...
  3. E

    Code now will not run

    Using Excel on MAC (Latest) My program worked fine a few days ago. I haven't done anything but now the start up macro fails. The error is:- Run Time Error '6' Overflow This is the code Sub MG01Apr57() Private Sub Workbook_Open() Dim Ans As String Ans = MsgBox("Update the Invoice Number"...
  4. B

    Convert excel formula to vba

    Hi I have formula that works in excel but doesn't work in vba. Formula: =A2 & " - " &D2 VBA: With .Range("AC2:AC" & lr) .Formula = "=A2 & " - " &D2" .Value = .Value End With It gives me error - Run Time Error, Type Mismatch Please help how to fix Regards Bill
  5. A

    VBA convert working wrong

    Hey community, i use following formula to convert time to numbers. Sub Convert() With Worksheets("DATA").Columns(10) .NumberFormat = "0" .Value = .Value End With End Sub But for some reason it is working wrong, instead of converting it replace most of cells with column name. ( column name...
  6. A

    convert VBA working wrong

    I use following formula to convert Time to numbers, but for some reason VBA converting some cells to column name ( column name is HOUR ) Can anyone tell me what is problem? Sub Convert() With Worksheets("DATA").Columns(10) .NumberFormat = "0" .Value = .Value End With End Sub
  7. P

    Cell change by selection Error

    I have the following code that won't work. Ideally, I would like for any cell in column Q in the chart to toggle values from "" to "P" upon selection. Also I need Range("L5") to toggle 5 different values upon selection Please see code below. Private Sub Worksheet_SelectionChange(ByVal Target...
  8. StuLux

    Trying to paste values only without Select/Copy

    Hi folks, long time since I came to this board for help and many thanks to all those who have helped me in past years. I'm trying to improve my code so that instead of actually selecting a range and then pasting as values I am trying to use the following code Sheet1.Range("B2:F" &...
  9. D

    VBA Code - Fine tuning code to extract data

    Hello, I have the below code which is on Sheet “Data” View Code andwhen it runs it takes approx. 2mins to run through 560 Rows. E.g. ‘Calculating (4 Threads): 27%’ When it runs its like it changes all cells in that column toone of the options then updates. Its working with what I’m trying to...
  10. A

    sumif with multiple criteria

    Hey community, i use following VBA Sub BonaquaASM1()Application.ScreenUpdating = False Dim Markets As Worksheet, lr As Long lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row Set Markets = Sheets("sheet4") Sheets("DATA").Range("A1:A" & lr).Name = "run"...
  11. C

    Sum of columns

    hi, I want sum of f2:h2 with the following formula, if i remove .value=.value , formula works fine but with .value=.value only summing f2:g2 Please help. With Range("d2").Formula = "=Sum(f2:h2)" .Value = .Value End With Thanks
  12. T

    VBA - Alternative to Activate to speed up macro

    Hi, I currently have a macro which is copying data from a form and pasting it into a log in a different workbook. The form is pulling data from an external database and thus takes a long time to refresh and my macro is looping this 262 times! At the moment it's taking circa 40 minutes to...
  13. D

    Multiple Criteria & Sum VBA Code

    Hello, Hoping someone can help me with he below. I have onemore I’m trying to add in, but I have no clue on how to do it. Last FreeDay Sheets(1) “Data” Column B:B = (Date Value) I Need toget a date value based on multiple criteria and sum 1st- Match Sheets(1) “Data” Column AZ...
  14. D

    Adding more code to existing Code

    Hello, I have the below code and I want to add more to theworksheet change code. Current Code I have Private Sub Worksheet_SelectionChange(ByVal Target AsRange) 'my formula on worksheet =INDEX('WharfSchedules'!B:B,MATCH(Data!AO59&Data!AQ59,'Wharf Schedules'!C:C&'WharfSchedules'!E:E,0))...
  15. D

    Worksheet Formula as VBA Code

    Hello, I'm trying to convert a work sheet formula to VBA. I enter a new job in a user form, when I hit continue and info transfers to "Data" sheet I want code to run and update. Sub Vessel_Details_WorksheetChange() 'my formula on worksheet =INDEX('Wharf...
  16. A

    VBA calculation takes too long

    Hey Dear community, I am using VBA+ formulas to analyz sales data ( about 1000,000rows ) it takes 7-8 minutes for calculation, i wanna know if there any other way to make it faster? because i need to calculate this report daily. I use following VBA for 80-90 different ranges on that report...
  17. T

    Macro Help for a Complete Novice!

    Hi guys, Macro basic level user here. I have created a macro but I need some help to get it a bit slicker as I have bolted it together like lego. I would also like the macro to repeat by looking at a list as opposed to me running it 200+ times. Current code: Sub NEXTINLIST() Dim v...
  18. M

    How to store the results of my for each loop If statement into another worksheet columns?

    I need to store the results of my for each If statement in the results worksheet of the active workbook. My loop works but I need to be able to store the results in column a and c from the results worksheet This is the code for the loop For Each cel In Range("A4:A85")...
  19. D

    Find criteria in a row then delete columns past but to a stop point

    Thanks Dante Amor for your assistance with the rows query. This is a separate query regarding deleting columns once criteria is met, then stopping at a point. I have a variable named value "MAX" which can be between 1 & 12. 1 to 12 is numbered in 'E3' to 'P3' of the worksheet, 'A3' to 'D3'...
  20. C

    Insert "X" every certain number of columns after finding "X", based on value of cell in same row

    Hello all, I am trying to write VBA to do a specific task, and I am almost there. I simply need a little bit of help to figure out how to do the remaining actions. Here is what I am trying to do with this code: 1) For rows 11 to 185, search range L11:DK185 for the cell value "X" 2) If "X" is...

Some videos you may like

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top