Re: Compatibility problem with a simple macro
Quote from cytop;672002Seems the section entitled 'Use a number format to hide zero values in selected cells' would cover that...
Thanks
Re: Compatibility problem with a simple macro
Quote from cytop;672002Seems the section entitled 'Use a number format to hide zero values in selected cells' would cover that...
Thanks
Re: Compatibility problem with a simple macro
Quote from cytop;671980Why not simply hide 0 values...?
Hello,
because there are few cells that I need 0 to be visible. So I delete zillion zeroes and fill few 0 i need manually.
N
Hello I wrote the following macro
Sub DeleteZero()
Dim WBook As Workbook
Dim WSheet As Worksheet
Dim Cell As Range
For Each WBook In Workbooks
For Each WSheet In WBook.Worksheets
For Each Cell In WSheet.UsedRange
If Cell.Value = 0 Then Cell.Clear
Next Cell
Next WSheet
Next WBook
End Sub
Display More
aim of this macro is to check all workbooks and worksheets and to clear content of its cells if value is 0. On one computer with Excel 2013 it works without a problem but on other computer with Excel 2007 it pops up error 400 with red circle if i start macro from developer tab or run time error 1004 if I start it from VBA editor. Can anyone helps me to solve this problem? I am newbie in this so any help is welcome.
Thanks,
N
Re: How to copy text from one table to another spot
Thank you it works
N
Re: How to copy text from one table to another spot
I want that for example you find text in cell a1 you copy it to f1 then it goes on so it find text in a54 it copy it to f2...etc
Hope that helps and thank you for your help i am total noob in this :(...
N
Re: How to copy text from one table to another spot
Hello,
It is the name of variable i used my own language kopija in English means copy
I would like to check all cells in used range if they have text and that text be copied or cut/copy for example in column f or optionally to another sheet.
I hope that this help,
N
Re: How to copy text from one table to another spot
Ok i did some other code changing and I come to this in my mind it should work but it do not nothing happens anyone can help me :)...the goal is the same how to identify a text cell in some range and to copy it into another place...
Sub MoveText()
Dim msg As String
Dim WBook As Workbook
Dim WSheet As Worksheet
Dim Kopija As String
For Each WBook In Workbooks
For Each WSheet In WBook.Worksheets
Select Case IsEmpty(ActiveCell)
Case True
msg = "is blank."
Case Else
Select Case ActiveCell.HasFormula
Case True
msg = "has a formula" `about this i do not care`
Case False
Select Case IsNumeric(ActiveCell)
Case True
msg = "has a number" `nor this`
Case Else
WSheet.Range("A1", WSheet.Range("A" & Rows.Count)) = Kopija
Range("Kopija").Copy Sheets("Sheet1").Range("F1")
End Select
End Select
End Select
Next WSheet
Next WBook
End Sub
Display More
Hello I am having a table like this
col2 | |||
sd | |||
fd | |||
sd | |||
dfssd | |||
q | w | g | |
dsfsd | |||
eee |
I need to test each cell for text and to copy that text into another place (lets say sheet 2). Size of table is variable and here is what i come up up till now
Sub MoveText()
Dim WBook As Workbook
Dim WSheet As Worksheet
Dim Cell As Range
For Each WBook In Workbooks
For Each WSheet In WBook.Worksheets
For Each Cell In WSheet.UsedRange
If Not IsNumeric(Cell.Value) Then Cell.Cut Range("A" & Cell.Row) `I do not know how do define the whole table range and how to say that program copy and paste only text cells`
End If
Next Cell
Next WSheet
Next WBook
End Sub
Display More
I tried to find sometihng simmilar but I was not able too and I am an newbie.
Thank you,
N
Re: Error 424 Object required
Thank you so much for your reply. Tough I am still puzzled why many write that if it does not work for the given case? Does it something to do with excel versions?
Re: Error 424 Object required
So in another word i do not need that .select thingy at all for pasting despite many people write it?
Re: Error 424 Object required
Quote from yegarboy;667090Code Display MoreSub Macro99() 'Step 1: Declare your variables Dim PP As PowerPoint.Application Dim PPPres As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide Dim SlideTitle As String 'Step 2: Open PowerPoint and create new presentation Set PP = New PowerPoint.Application Set PPPres = PP.Presentations.Add PP.Visible = True 'Step 3: Add new slide as slide 1 and set focus to it Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly) PPSlide.Select 'Step 4: Copy the range as a picture Sheets("Slide Data").Range("A1:J28").CopyPicture _ Appearance:=xlScreen, Format:=xlPicture 'Step 5: Paste the picture and adjust its position PPSlide.Shapes.Paste PPSlide.Shapes.Select 'After Pasting Select the Shape. This is a separate line of code. PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True 'Because this line deals with "Selection" is why PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True 'Step 6: Add the title to the slide SlideTitle = "My First PowerPoint Slide" PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle 'Step 7: Memory Cleanup PP.Activate Set PPSlide = Nothing Set PPPres = Nothing Set PP = Nothing End Sub
Now it says Compile error method or data member not found. for added line
Re: Error 424 Object required
One more thing if i type
PPSlide.Shapes.Paste instead of PPSlide.Shapes.Paste.Select then I do not get the error and macro finish task in full but I still can not figure out why .selection part is causing problems.
Thank you for help,
N
Hello,
I am in process of learning VBA usage for Excel and I encountered an problem which I can not solve. I typed in this code from a book and I am getting Error 424 at some point of code. Code should copy range of cells from excel to power point in form of picture. When I execute it it copy range to some extent and then it shows this error 424. When i hit debug button it says that error 424 is showing on PPSlide.Shapes.Paste.Select line...tough some copy pasting did occur.I tried to solve that by myself but I can not so I hope that someone will help me thank you. I am using excel 2013. I am also attaching excel file.
Sub Macro99()
'Step 1: Declare your variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideTitle As String
'Step 2: Open PowerPoint and create new presentation
Set PP = New PowerPoint.Application
Set PPPres = PP.Presentations.Add
PP.Visible = True
'Step 3: Add new slide as slide 1 and set focus to it
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
PPSlide.Select
'Step 4: Copy the range as a picture
Sheets("Slide Data").Range("A1:J28").CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
'Step 5: Paste the picture and adjust its position
PPSlide.Shapes.Paste.Select
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'Step 6: Add the title to the slide
SlideTitle = "My First PowerPoint Slide"
PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle
'Step 7: Memory Cleanup
PP.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set PP = Nothing
End Sub
Display More
Re: SUmming up two collums with mixed data
Quote from StephenR;666722
Thank you it works as a charm and is so more elegant then my semi solution.
N
Re: SUmming up two collums with mixed data
Quote from StephenR;666696Please add code tags as per the forum rules to which you agreed.
The SUM formula will ignore text.
ok i did that
Hello I am newbie with VBA coding and I am trying to make code which will in essence summ up two numbers in one row and write it to another colum in the same row
here is the table example
col2 | col3 | ||
sd | in here value is 5 i.e 1+4 | ||
fd | 5...etc | ||
sd | |||
dfssd | |||
q | w | ||
dsfsd | |||
eee |
As you can see in each colum has empty cells, cells with numerics and text. I am interested only in numeric cells
THe problem is that size of the table is changing
I managed to come with part of the code which goes like this
Sub SumCol()
Dim WBook As Workbook
Dim WSheet As Worksheet
Dim Cell As Range
Dim cnt As Long
For Each WBook In Workbooks
For Each WSheet In WBook.Worksheets
For Each Cell In WSheet.UsedRange
If IsNumeric(Cell) = True Then cnt = ......in here i do not know how to say that row in col 2 and col 3 should be summed and how the value should be written in col 2+3 for each set of data within the used range
Next Cell
Next WSheet
Next WBook
End Sub
Display More
I hope that you can help me,
Thank you,
N