Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA to find cell with a specific value, and ID its coordinates

hi again

need help with finding a cell which contains a number, in a column (ie not the WHOLE sheet) and ID the cell's coordinates in number number form, instead of letter number, and be able to use those numbers. like when you say, activesheet.cells(row, col) instead of activesheet.range("a3") etc. i will need to be able to use the coordinates of the cell

the number has been calculated inside vba itself, don't need to worry about that

It also needs to be able to be used on different sheets.

thank you so much!


Post your answer or comment

comments powered by Disqus
Hi All,

Just wondering if anyone knows if this is possible and potential way of doing it,

"Find cells of a specific colour and delete value"

Many Thanks
Sam

Hi
I am new to this forum and is a VBA novice.

I am trying to find all cells with a negative value and mark them (maybe mark the whole row) so I can find them easier. How do I do that?

Another issues I have is that I need to find cells with a certain value and multiply them eg
if cellA has a value of 7 and cell b is not 2 then multipy by 8, if cell b is 2 multiply by 2.

Hope one of you experts can help:-)

Thanks

Katja

this may be simpe for you pros, but kinda difficult for me. have a basic worksheet with client information. if i need look for a specific value and pull it out as a list, which fx formula do i use or if you have a custom one for me.

also, if i have to search entire worksheet and multiple workbooks for the same value, what formula do i use. thanks!

thanks.

i dont know if this is possible but il give it a shot. i want to link a cell
with a letter value in it to a cell with a formula in it. so whenever a cell
with a specific letter value appears, in the cell ive linked it to, a
specific formula linked to that letter value will appear.
eg, D appears in one cell, the cell linked to it should display; =-A10
if H appears in the cell, the cell linked to it should display; =+A10
(or somthing like that)

at the moment im putting in the formula manually next to every cell where
the letter appears and it would save me a great deal of time if i could
figure this one out, any help would be appreciated

thanks
tom

First I want to say Hello all. I am just now really getting into heavly using excel and VBA's. I am taking a class on excel this semester and classes on VBA and SQL programming next semester. I am excited to learn a new and valuable skillset. I am also looking forward to contribuiting to the forum.

Ok so here is my question. I have a workbook that changes month to month on the number of tabs it has. The reason for this is it is used to import Journal Entries into an accounting software. It is much faster doing it this way rather than have someone else manually enter them. All of the Workbooks contain the same number of columns. However, the number of rows varry. The workbooks are set up like this:

A B C D I
Account number Debits Credits Description Reference

First I want to name all worksheets in the workbook based on the value in cell I1. Second step the accounting software we use sees Credits as Negative numbers so what I need to happen first is for the macro to look in column C and when it finds a cell with a number in it, copy the Account number in A and move it to the next open cell in Column A and then in the same row in column B copy the number from C and paste as a Negitive value in B and so on. Then I want the macro to delete the entire column D and shift all other columns to the left so that D now becomes C. Also deleting columns H and I. I would also like the macro to save each worksheet as a new workbook in G:UsersAndy2011GJ's, Pr's, GJTR's, GJTPr's

I have created the following macros but only the rename worksheets macro does all worksheets at once. I can not figure out the part where it will find a value in C and copy the account number and move it to the next open cell in A and copy and paste the value in C as a neg. In case this doesnt make sense Ill give an illustration.

A B C D
1 60 0 Example
5 55 10 Example
3 40 0 Example
9 0 20 Example

I need it to look like this:
1 60 0 Example
5 55 10 Example
3 40 0 Example
9 0 20 Example
5 -10 0 Example
9 -20 0 Example

and so on there may be only 2 rows but there could be 100 it varies.

I have the following macros already. For explanation purposes I am going to number each macro.

Macro 1.

Change name of worksheets in workbook
Sub Worksheet_Name_GJ_Import_Uses_Cell_I1()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
ws.Name = ws.Range("I1").Value
If Err.Number <> 0 Then
Err.Clear
Exit Sub
End If
Next ws
End Sub

Macro 2.

And
used to ensure all cells in B are a value and to delete C and move D to C then Delete H and I.
Sub GJ_CLEANUP_PRIOR_TO_IMPORT()
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("H:I").Select
Selection.Delete Shift:=xlToLeft
Dim LastRow As Long, n As Long

LastRow = Range("B65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, 2).Value = 0 Then Cells(n, 2).EntireRow.Delete
Next n
End Sub

Macro 3.

And then I use this to delete all rows that have a zero in B.
Sub Delete_ALL_ROWS_EQUAL_TO_0_Column_B()
Dim LastRow As Long, n As Long

LastRow = Range("B65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, 2).Value = 0 Then Cells(n, 2).EntireRow.Delete
Next n

End Sub

I am asking for help putting all of these together into 1 macro and adding the things from above added in. The Copy and pasting based on C would go inbetween Macro 1 and 2. And the make each worksheet a new workbook and saved in G:UsersAndy2011GJ's, Pr's, GJTR's, GJTPr's Would be last. I have played around with trying to build a macro for this but I have not been successful. I hope What I am asking is clear. Thanks a ton and hopefully someday soon I will be able to be one of the ones answering the questions instead of asking them. Andy

Hi,

I am trying to create a macro that finds, on a worksheet, all cells with a
given value, for example "a", and then selects them. ANy ideas??

Sorry I'm not great with macros.
I found a macro before to move a whole row to another worksheet, if a certain value in a certain column was matched.
however I could really do with just moving three specific cells of the row, to another sheet.

Basically, I have a 'To do' list, where you can select what priority they are. If they are selected as 'done', then I would like them to move to the 'completed' sheet.

It should hopefully make completed sense when you see the sheet

So if any group of 3 cells (e.g. E10:G10), from either sheet 'To do Now' or 'To do Autumn', is selected as '4 - Done', then the 3 cells move to the relevant area (column E:G, at the next available free row)
Many thanks in advance

Hi,

Bit of a newbie struggling at the moment. I have columns A to L autofiltered, with the column headings in row 1, so that the values I am filtering by are in rows 2 and on. Now, I have got as far as filtering by column C and then by column D. Now I want to find the cell in column L that has the highest value. I can select the column by visible cells only, but cannot figure out how to move to the cell with the highest value. Would anyone be able to help please?

Many thanks,

Igzilla.

How do I use SpeciaCells to select cells in a range with a certain value?

In the same manner that you can use SpecialCells to select blank cells and then delete that entire row I want to do the same for a cell containing a specific value/text

I have been using SpecialCells to assign a value in blank cells as a temporary marker. I then need to delete the rows with the temporary markers. I have not been able to use SpecialCells to select the temporary markers . Any help will be appreciated

Thanks

SJ

Is there any formula to count the number of cells (and total the numerical
content of those cells) with a specific color pattern?

Hello,

I am looking to tally up cells with a value of either "Y" or "N/A" to determine what percentage of tasks have been completed or are not applicable.

Please see my attached example:
Tasks are in columns E through K. If that task is completed, we enter in a "Y" or if it is not applicable for that row, "N/A".

In row N I want to know how many of the tasks are complete. I don't want to hard code any numbers in because we may add/delete tasks or rows. So I would like the formulas to accommodate for that.

in Row 8 I want to total the number of "Y" or "N/A" as a percentage of them complete also. Again, I'd like that to be dynamic if I need to add items.

Does anyone have any thoughts?

Thank you!

Hi, this macro is supposed to copy all the rows with a specific value (in
C25) to another sheet. But, only the first row will be copied. Can someone
help?? Or have a better suggestion??

Sub Copy()
Application.ScreenUpdating = False
With Sheets("Sheet1")
Dim i As Long, sTargetValue As String
sTargetValue = Sheets("Sheet1").Range("C25")
For i = 100 To 1 Step -1
If Cells(i, "A").Text = sTargetValue Then
Rows(i).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
End If
Next i
End With
End Sub

In an array of cells, how do I find a cell containing a certain value and get
Excel to output the cell reference (or column reference)?

Hello there,

I'm looking for VBA code to check entire column A in specific sheet "sheet3" on a specific value "SEC55B". If there is a value change it to "SEC550".

Can somebody help me out please.

Thnx a lot
Aquila

I want a function which enables me to search through data in a table for a specific value and get corresponding row and column headers.table.jpg

Hi everybody,

I have a spreadsheet that I'm using as a call log for a support desk. Sometimes, when the problem is outside of my scope, I have to call in a ticket to a helpdesk.

I want to keep the call log separate from the ticket list, but I want to be able to reference a ticket number for a specific call or vice versa using a command button.

Here's the psuedocode for my problem:
Sub GoTo_TicketNumber()
'When the button is clicked the value in the active cell is captured (search_Value)
Worksheet 1 ("Call Log") Is deactivated
Worksheet 1 ("Ticket List") Is activated
For each cell in the now active sheet
Search for the search_Value
If found
Go to the cell where the value is and place the cursor there
Else:
Output message "No ticket list for related call number"
End If
Next cell
End Sub

I will already have the workbook open with the call log and the ticket list worksheets so there's no need to open a workbook. However, I have 2 additional worksheets in this workbook that don't need to be searched because the data in these two is significantly different than the others and, I feel, would only slow the search time.

I have some programming experience, but I'm self taught with VBA, so I struggle at times. If the solution for this problem is right in front of my face, please go easy on me.

Thank you for any help you can toss my way,
beacon

I'm looking at Chip Pearson's VBA code to eliminate blank cells (copied below) and realise that this will not work with cells that have a " " value. Is there any way to modify this so that it will return values from a list containing those " " values?

Function NoBlanks(RR As Range) As Variant
    Dim Arr() As Variant
    Dim R As Range
    Dim N As Long
    Dim L As Long
    If RR.Rows.Count > 1 And RR.Columns.Count > 1 Then
        NoBlanks = CVErr(xlErrRef)
        Exit Function
    End If
    
    If Application.Caller.Cells.Count > RR.Cells.Count Then
        N = Application.Caller.Cells.Count
    Else
        N = RR.Cells.Count
    End If
    
    ReDim Arr(1 To N)
    N = 0
    For Each R In RR.Cells
        If Len(R.Value) > 0 Then
            N = N + 1
            Arr(N) = R.Value
        End If
    Next R
    For L = N + 1 To UBound(Arr)
        Arr(L) = vbNullString
    Next L
    ReDim Preserve Arr(1 To L)
    If Application.Caller.Rows.Count > 1 Then
        NoBlanks = Application.Transpose(Arr)
    Else
        NoBlanks = Arr
    End If
End Function


How can I extract all the cells from a large range that start with a spesific value spesified in a cell.

I can count them with count if but when I try to display only those cells I get all the cells that has the same values but not starting with it.

Please help

Hi everyone,

I have a table of values, I have used the max function to return the highest value found. Now I need to know where that value lies, as the table is quite large. After reading through forums I've found a way for excel to return the cell location, but only for a single column or single row (Using the Address, Match and / or Index Functions). I need to find a way to do this for a table of values.

Any help would be much appreciated!

Thanks!

I have arange of cells in a workbook, i need the vba to find in another workbook.

your help is appreciated

First part: Looking for VBA code that searches for cells with a line above them (xlEdgeTop) and puts the contents of that cell in Bold. The range where to look in is only 1 column. Also, if the top edge border has been made with BorderAround, would XL still find this cell or are the xledges not a part of borderaround?

I am using a spreadsheet to track print jobs. One of the things I track is
the type of paper used and the cost of each piece for each job. Each paper
stock is assigned a stock code and has a unique cost. I want to be able to
enter the code and have the cost of the paper automatically come up in the
next cell. I currently have 16 different codes and each one has a different
cost. For instance, I want to enter the code PS1 in cell K5 and want it to
put the cost associated with PS1 (.03) in cell L5. I have tried to use the IF
function, but you can only nest 7 in a cell (I think). Any ideas? Any help
would be appreciated. Thanks!

I have a sheet with 2884 rows.. I am currently using the following macro (with changes for each column) to search columns AN - BG and delete cells with a value of 0 and move the columns that do not = 0 up..

for example:

0
0
0
0
5
0
0
3
0
0
0
1

would be

5
3
1

I can not delete the entire row, because the next column may not have a 0 value in the same row..

Obviously, the process I have below takes FOREVER. Is there a faster way to do this? Again, it is Column AN through Column BG and rows 6-2884 . I am going through it backwards so that I do not miss any 0's.

Dim ANcell As
Range
Dim ANCount As Long
  For ANCount = 2884 To 6 Step -1
  'Start from bottom of range, so we don't skip any rows When we've deleted one
  If Trim(Range("AN" & ANCount).Value) = "0" Then
     Range("AN" & ANCount).Delete Shift:=xlUp
  Else
  End If
  Next ANCount


Hi,

I am not a new user of Excel but i think this would require use of a macro which i am new to. Probably this wud be very straightforward for you, but i wanted to delete cells containing values "0" and "1" in all the columns. Could you please help me with that.

Thanks,
Aman


No luck finding an answer? You could always try Google.