Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with found a value on a column
Hi there,
I have a little problem making an excel macro, I want that the macro find the last value (the zero cell) in a column an change this value. I think that is like for SearchQuery = . range(A1:A100) if SearchQuery.Value = "0" Then SearchQuery.Value = "I found it" end if This work fine when I try to change the value of an specific cell, but I don't know the statements when is a complete column, for the first zero value and the coude upstair looks fine, But it doesn't work, could any can help me? Thanks for ur comments =) David Garcia --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with found a value on a column
Sub tester1()
Dim cell As Range Set cell = Range("A1") Do While True If IsNumeric(cell) Then If CLng(cell) = 0 Then Exit Do End If Set cell = cell.Offset(1, 0) Loop cell.Value = "I Found it" End Sub -- Regards, Tom Ogilvy "Iceman Solope " wrote in message ... Hi there, I have a little problem making an excel macro, I want that the macro find the last value (the zero cell) in a column an change this value. I think that is like for SearchQuery = . range(A1:A100) if SearchQuery.Value = "0" Then SearchQuery.Value = "I found it" end if This work fine when I try to change the value of an specific cell, but I don't know the statements when is a complete column, for the first zero value and the coude upstair looks fine, But it doesn't work, could any can help me? Thanks for ur comments =) David Garcia --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with found a value on a column
By the way I was tried this two codes but doesn't work, could u help m
plz? :D Code ------------------- Sub Searching() Dim rng As Range Dim lngRow, lngLastRow As Long Application.ScreenUpdating = False Set rng = ActiveCell lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row For lngRow = lngLastRow To rng.Row + 1 Step -1 If Cells(lngRow, 1).Value = rng.Value Then Cells(lngRow, 1).Value = "Che perro" End If Next lngRow Application.ScreenUpdating = True End Sub ------------------- Code ------------------- Sub searching() Dim rng As Range Dim lngRow, lngLastRow As Long Application.ScreenUpdating = False Set rng = ActiveCell If Range("c1").End(xlDown).Offset(0, 1).Value = rng.Value Then Range("c1").End(xlDown).Offset(0, 1).Value = "123" End If End Sub ------------------- -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with found a value on a column
Neither code sample does anything like what you describe. Might be one
reason they don't work. -- Regards, Tom Ogilvy "Iceman Solope " wrote in message ... By the way I was tried this two codes but doesn't work, could u help me plz? :D Code: -------------------- Sub Searching() Dim rng As Range Dim lngRow, lngLastRow As Long Application.ScreenUpdating = False Set rng = ActiveCell lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row For lngRow = lngLastRow To rng.Row + 1 Step -1 If Cells(lngRow, 1).Value = rng.Value Then Cells(lngRow, 1).Value = "Che perro" End If Next lngRow Application.ScreenUpdating = True End Sub -------------------- Code: -------------------- Sub searching() Dim rng As Range Dim lngRow, lngLastRow As Long Application.ScreenUpdating = False Set rng = ActiveCell If Range("c1").End(xlDown).Offset(0, 1).Value = rng.Value Then Range("c1").End(xlDown).Offset(0, 1).Value = "123" End If End Sub -------------------- --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with found a value on a column
I see that,
I have and specific question, How can I put into my code the "empty value, because this statement works, but only when the cells have th specific value, and know, I think it shuld be works on the first cel that doesn't have the value. Code ------------------- Sub Busqueda() Sheets("hola").Columns(2).Find _ (What:="hola", After:=[B1]).Replace _ What:="hola", Replacement:="Richard" End Sub ------------------- thnks for ur fruitfull help Davi -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with found a value on a column
I gave you code the does what you describe.
this code won't work. to the best of my knowledge, you can't replace an empty cell with a value. Also, if you could, you would fill up the whole column. -- Regards, Tom Ogilvy "Iceman Solope " wrote in message ... I see that, I have and specific question, How can I put into my code the "empty" value, because this statement works, but only when the cells have the specific value, and know, I think it shuld be works on the first cell that doesn't have the value. Code: -------------------- Sub Busqueda() Sheets("hola").Columns(2).Find _ (What:="hola", After:=[B1]).Replace _ What:="hola", Replacement:="Richard" End Sub -------------------- thnks for ur fruitfull help David --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with found a value on a column
This code works fine
Code ------------------- Sub SearchAndReplace() Range("D1").Select ' or any Cell Selection.End(xlDown).Select 'Goes down to the last Cell with content (also possible with UP) ActiveCell.Offset(1, 0).Range("D1").Select 'Goes one row deeper If ActiveCell.Value = "" Then ActiveCell.Value = "Che perro" End If End Sub ------------------- By the end of this code run, I am on an active cell that is on the las empty cell (the ones that I changed), now I want to go to the firs column, in the row where I am. could any know how can I do this? Thnks for ur comment :) David Garci -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with found a value on a column
Sure, but why bother to ask?
-- Regards, Tom Ogilvy "Iceman Solope " wrote in message ... This code works fine Code: -------------------- Sub SearchAndReplace() Range("D1").Select ' or any Cell Selection.End(xlDown).Select 'Goes down to the last Cell with content (also possible with UP) ActiveCell.Offset(1, 0).Range("D1").Select 'Goes one row deeper If ActiveCell.Value = "" Then ActiveCell.Value = "Che perro" End If End Sub -------------------- By the end of this code run, I am on an active cell that is on the last empty cell (the ones that I changed), now I want to go to the first column, in the row where I am. could any know how can I do this? Thnks for ur comment :) David Garcia --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colum is not found thats missing a column B how to find it | Excel Worksheet Functions | |||
return text value found most frequently in a column | Excel Worksheet Functions | |||
Formula to search for given term, and if not found in column to add it to list | Excel Worksheet Functions | |||
How to find a row since I found a value in a column? see example | Excel Worksheet Functions | |||
Hi-light area based on name found in column A | Excel Programming |